Retrieving data in multiple closed workbooks


New Member
Nov 22, 2005

Is it possible to writing a macro that looks up data in another workbook retrieves it from the workbook and moves onto the next workbook in the directory and so on until it has looked at all of the workbooks? All workbooks are identical other than the information contained in them. I could have up to 100 workbooks.

Please help

Excel Facts

Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
hello ijswalker:
Yep ! :wink:

Here's an example ...

Public Sub GetDirXlsContents()
' Source sheet name, Source directory path, Source cell Range
Call CopyFromEachFileInPath("Sheet1", "C:\test", "A1:I500")
End Sub

Private Sub CopyFromEachFileInPath(SheetName, Path, Rng)
    Dim fs, f, f1, fc, s
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFolder(Path & "\")
    Set fc = f.Files
    ' make a temp sheet
    Application.ScreenUpdating = False
    TargSh = ActiveSheet.Name
    TempSh = ActiveSheet.Name
    Application.ScreenUpdating = True
    For Each f1 In fc
     With Sheets(TempSh)
    ' clear temp sheet and start again
    ' Place Src Info on Temp Targ Sheet
    If Right(f1.Name, 3) = "xls" Then
    fName = Left(f1.Name, Len(f1.Name) - 4)
    .Range(Rng).FormulaArray = "='" & Path & "\[" & fName & "]" & SheetName & "'!" & Rng
    .Range(Rng).Value = .Range(Rng).Value
     'GetValuesFromAClosedWorkbook Path, f1.Name, SheetName, "A1:K30"
    End If
    ' if columD = 1 copy over
    For Each A In .Columns("A:A").SpecialCells(xlCellTypeConstants, 3)
        NxRw = Cells(65536, 1).End(xlUp).Row + 1
        If Not A.Value = 0 And A.Offset(1, 0).Value = 0 Then   ' copy to final sheet
        Range("A" & NxRw & ":I" & NxRw).Value = .Range("A" & A.Row & ":Z" & A.Row).Value
        Range("J" & NxRw).Value = fName
        End If
    Next A
    End With
    ' have user see list build, so know not frozen
    Cells(NxRw, 1).Select
    Next ' workbook
    ' get rid of temp sheet
    Application.DisplayAlerts = False
    Application.DisplayAlerts = True

End Sub
Upvote 0
Data Row

Unbelievable, That is quite amazing. It pulls every Cell that is populated. Is there a way to select the individual Cells in the workbook rather than a range. I am trying to pull the data from certain Cells to end up with one row of data that relates to one workbook.

Once again - quite amazing!!!!
Upvote 0
Since this last posting I have figured out a way of putting my data in rows. So disregard that last question. However, this leads me to a new problem.

Is there a way of Retrieving text from text boxes in closed excel files and putting it into rows in order for my form to pick up the text?

E.g, I have 4 text boxes in my excel workbook. I want to pull the info from each of the text boxes and have the text put into different cells all in the same row. Can I also keep the formatting from the text box.


Upvote 0
I have one more question relating to the solution I was furnished with before. The code allows me to select a range from the closed workbooks which is great. However, the workbooks I have now have different data ranges that I am trying to transfer in order into my master database.

Is there a way of looking into each of the workbooks - extracting the rows of data and then moving onto the next workbook and extracting a different number of rows of data?

For illustration purposes. My first workbook will have 5 rows of data and my second workbook will have 6 rows of data. I would like to have 11 rows of data in my master workbook.

Hope you can help?


Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back