Open Files listed in cells using VBA

[MTBer]

I have a list of filenames starting in cell A1 and going down the
column. The list can be of varying length. ie

Book1.xls
Book2.xls
book3.xls

I need to open each of these books in turn, and perform a macro (this
part of the code is already written).

What is the best method for opening each of these files in turn and
performing the macro?

thanks

 

[Frank Kabel]

Hi
assuming these files are all in the same directory try something like
the following:

sub foo()
dim source_wbk as workbook
dim source_wks as worksheet
dim source_rng as range
dim cell as range
dim target_wbk as workbook
dim path
dim fname

set source_wbk=activeworkbook
set source_wks=source_wbk.worksheets("sheet1")
set source_rng=source_wks.range("A1:A20")
path="C:\temp\"

for each cell in source_rng
if cell.value <>"" then
fname=cell.value
workbooks.open path&fname
set target_wbk=activeworkbook
msgbox target_wbk.name
'perform your code
target_wbk.save
target_wbk.close
set target_wbk = nothing
end if
next
end sub

 

[Bob Phillips]

Firstly, it is probably best that your macro takes a workbook as a
parameter, and does it's stuff
upon that. Something like

Sub myMacro(wb As Workbook)

With wb
'do your stuff
End With
End Sub

and then the other code, assuming the names in column A

Set sh = Activeworkbook.Activesheet
With sh
For i = 1 To .Cells(Rows.Count,"A").End(xlUp).Row
If .Cells(i,"A") <> "" Then
Set wb = Workbooks Open FileName:= .Cells(i,"A")
myMacro wb
wb.Save
wb.Close
End If
Next i
End With

You should be aware that this presumes that the files are in the same
directory as the current workbook, else you need to set the path as well.


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Frank Kabel]

Hi Bob
small typo / a missing dot

For the OP: change
Set wb = Workbooks Open FileName:= .Cells(i,"A")

to
Set wb = Workbooks.Open FileName:= .Cells(i,"A")