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.
This question is related to
excel
excel-2010
vba
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
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
You have to add this line just before loop
MyFile = Dir
Loop
Source: Stackoverflow.com