VBA to open all Excel files in a specified folder

henry fam

New Member
Joined
Nov 25, 2013
Messages
15
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

Board Regular
Joined
Nov 28, 2013
Messages
225
Not sure. Same idea works for me. I'm hoping you haven't put 1013 instead of 2013 in the MyFolder string ?
 

henry fam

New Member
Joined
Nov 25, 2013
Messages
15
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

New Member
Joined
Aug 19, 2014
Messages
5
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.
 

Forum statistics

Threads
1,085,843
Messages
5,386,297
Members
401,992
Latest member
CleverHopper

Some videos you may like

This Week's Hot Topics

Top