VBA to open all Excel files in a specified folder

[henry fam]

Hi:
I want to open all Excel files in a specified folder and have the following code, but it didn't work:

Sub OpenFiles()
Dim MyFolder As String
Dim MyFile As String
MyFolder = "\\G:\Functional Teams\Finance\2013\Consolidation & Reporting\1013\Financial Package"
MyFile = Dir(MyFolder & "\*.xls")
Do While MyFile <> ""
Workbooks.Open Filename:=MyFolder & "\" & MyFile
MyFile = Dir
Loop
End Sub

It gave me a message Run-time error'52' "Bad file name or number", and the error was highlighted in the line MyFile = Dir(MyFolder & "\*.xls")
Why was it? How should I correct it?
Any help is very appreciated. Or please advise me other codes that work.
I'm using Excel 2010.
Thanks
Henry

 

[lexxie2013]

Not sure. Same idea works for me. I'm hoping you haven't put 1013 instead of 2013 in the MyFolder string ?

 

[henry fam]

Hi Lexxie:
I changed to a newa path, and it did work. My goodness. Thank you so much for your confirmation.
By the way, can you help me with another thing:
I want to add another macro to this vba code in order to change the layout of these reports including,
unprotect sheet,
Print Titles: portrait; margin: 0.25 for each side; center on page : horizontally.
Please help.
Thanks so much
henry

 

[scottyblaze]

I found this macro to be very useful, but is there any way to remove the predefined folder location and replace it with a prompt to allow the user to specify the location? I'd like to be able to share this with other people who are not familiar with the developer functions of excel. Thanks.