opening files in a loop

[stevesail]

I have an open worksheet (summary.xls) that contains unique number
(e.g. 2012) in column 1. Those unique numbers are also used as th
filenames of other workbooks (e.g. 2012.xls) that I need to access.
I'd like to loop through the unique values in column 1 of summary.xls
successively opening the corresponding .xls file, copying some value
out of that opened file back into summary.xls. How do I open thos
other files (eg. 2012.xls) and make them the active workbook, so I ca
copy the info from them, then close those files.
I know how to open an individual file from a dialog and make it activ
via:
Application.FindFile
FileN = ActiveWorkbook.Name
But using an Open "filename" statement, then
FileN = ActiveWorkbook.Name
doesn't make the "opened" file active

 

[Ron de Bruin]

Try this example

It will loop through the cells in the B column of the first sheet
and will open the file fpath = "C:\Data\" & cell.Value & ".xls"
Change the path to yours

It wil copy cell A1 from the first sheet of every file
in the first sheet (A column)of the workbook where the code is in of every file.


Sub TestFile1()
Dim cell As Range
Dim WB As Workbook
Dim a As Long
Dim fpath As String
Dim basebook As Workbook
Set basebook = ThisWorkbook
a = 0
Application.ScreenUpdating = False
For Each cell In basebook.Sheets(1).Columns("B").Cells.SpecialCells(xlCellTypeConstants)
fpath = "C:\Data\" & cell.Value & ".xls"
If Dir(fpath) <> "" Then
a = a + 1
Set WB = Workbooks.Open(fpath)
WB.Sheets(1).Range("A1").Copy basebook.Sheets(1).Cells(a, 1)
WB.Close False
End If
Next
End Sub