macro - open all files in a folder

Question

I want to open all files in a specified folder and have the following code

Sub OpenFiles()
Dim MyFolder As String
Dim MyFile As String
MyFolder = "\\ILAFILESERVER\Public\Documents\Renewable Energy\FiTs\1 Planning
           Department\Marks Tracker\Quality Control Reports"
MyFile = Dir(MyFolder & "\*.xlsx")
Do While MyFile <> ""
Workbooks.Open Filename:=MyFolder & "\" & MyFile
Loop
End Sub

The problem I have is that it just keeps trying to open the first file in the folder repeatedly and won't move on. Can anybody help, I'm a bit of a novice at VBA and could really do with some assistance. I'm trying to open around 30 reports that are all in .xlsx format. Many thanks in advance.

Do you want to open all of the files in a folder at the same time? — Anderson Green, Feb 23 '13 at 04:47

score 33 · Answer 1 · answered Jun 22 '12 at 08:58

33

You have to add this line just before loop

    MyFile = Dir
Loop

answered Jun 22 '12 at 08:58

Siddharth Rout

147,039
17
206
250

6

Hi, Ross and welcome to StackOverflow! Here, it's customary to thank people by clicking the 'accept as answer' button next to their answer. It's shaped like a big checkmark. This way your helper gets points as a reward for helping you and you get credit for contributing a helpful Q&A page. – Harald Brinkhof Jun 26 '12 at 22:30

score 3 · Answer 2 · answered May 19 '15 at 04:25

You can use Len(StrFile) > 0 in loop check statement !

Sub openMyfile()

    Dim Source As String
    Dim StrFile As String

    'do not forget last backslash in source directory.
    Source = "E:\Planning\03\"
    StrFile = Dir(Source)

    Do While Len(StrFile) > 0                        
        Workbooks.Open Filename:=Source & StrFile
        StrFile = Dir()
    Loop
End Sub

score 1 · Answer 3 · edited Apr 26 '15 at 23:30

Try the below code:

Sub opendfiles()

Dim myfile As Variant
Dim counter As Integer
Dim path As String

myfolder = "D:\temp\"
ChDir myfolder
myfile = Application.GetOpenFilename(, , , , True)
counter = 1
If IsNumeric(myfile) = True Then
    MsgBox "No files selected"
End If
While counter <= UBound(myfile)
    path = myfile(counter)
    Workbooks.Open path
    counter = counter + 1
Wend

End Sub

macro - open all files in a folder

3 Answers3

Linked