VBA openworkbooks using file variable

[jimsjams]

Hello all and Happy Friday,

I was hoping somebody might know if it is possible to open files using a variable and loop. I want to define the name and path of the files so they can be easily changed later and then loop through them so that I can extract some data from each one.

I thought something like this should work:

Sub openworkbooks()

myfile1 = "c:\test1.xls"
myfile2 = "c:\test2.xls"
myfile3 = "c:\test3.xls"
myfile4 = "c:\test4.xls"
myfile5 = "c:\test5.xls"

For i = 1 To 5

Workbooks.Open Filename:=myfile & i
'copy some data from workbook

Next i

End Sub

This gives an error saying that 'myfile1.xls' doesn't exist.
However if i use workbooks.open Filename:=myfile1 then that would work and open the correct workbook.

Any ideas?

Thanks,

James

 

[RoryA]

Try:

Sub openworkbooks()
Dim myFile(1 to 5) as String
myfile(1) = "c:\test1.xls"
myfile(2) = "c:\test2.xls"
myfile(3) = "c:\test3.xls"
myfile(4) = "c:\test4.xls"
myfile(5) = "c:\test5.xls"

For i = 1 To 5

Workbooks.Open Filename:=myfile(i)
'copy some data from workbook

Next i

End Sub

 

[Domski]

Maybe:

Sub openworkbooks()
Dim i As Long
For i = 1 To 5
Workbooks.Open Filename:="C:\test" & i & ".xls"
'copy some data from workbook
Next i
End Sub

Dom

 

[jimsjams]

Rory and Dom,

Thanks so much for the help. Works perfectly.

Rory - I think that's twice you've helped me already this morning and it's only 9am here!

Thanks again,

James

 

[RoryA]

It's a slow Friday!

 

[Domski]

It's a slow Friday!

Isn't it always?

 

[RoryA]

I wish it were!!