This is a short but sweet VBA code that let’s you open all Excel files in a folder without having to select them all. The code asks you for a folder location and it does the rest :). At the moment the code opens all .xlsx files from within the selected folder, but that can be changed in the code. You can clearly see this on line 3 of the code.
Sub XET_OpenAllXfilesInFolder()
fldr = GetFolder(ActiveWorkbook.Path)
xfile = Dir(fldr & "\*.xlsx")
Do While xfile <> ""
Workbooks.Open Filename:=fldr & "\" & xfile
Loop
End Sub
This Code can also be found in an Xnet Excel Tools AddIn for Excel which is free and can be downloaded here.