VBA macro that will open all Excel files in a folder

Macro that will open all Excel files in a folder.  This functionality can be used to aggregate multiple workbooks into one or pull data from all workbooks in a folder.  This demo example only opens the files.  Within the loop you will add your logic to be performed on the opened workbook.


Sub LoopAllFiles_inFolder()
MsgBox ("Select folder with files to loop")
With Application.FileDialog(msoFileDialogFolderPicker)
    .AllowMultiSelect = False
    On Error Resume Next
    strpath = .SelectedItems(1)
    On Error GoTo 0
End With
If Right(strpath, 1) <> "\" Then strpath = strpath + "\"
ChDir strpath
strextend = Dir("*.xls*")

Do While strextend <> ""
    Set wb1 = Workbooks.Open(strpath & strextend)
    ' add logic to be performed on open workbooks here

strextend = Dir

End Sub

Interested in learning new skills?