Automatically open Excel Workbooks in Folder and Run VBA Code

If you have multiple Excel workbooks that have to have the same process completed in each it can become quite laborious to complete the task manually.  A quick and easy way to speed up your work processes is to do this automatically using Excel VBA.

Automatically Open Excel Workbooks in a Folder

With Application.FileSearch
.LookIn = "ExcelGeek\VBA\Learning\" – This specifies the folder you want to search within, including the path
.FileName = ".xls" – The type of files you wish to open, here I have specified Excel by using the .xls. If you want Word, then type .doc.
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then

Set newworkbook = ActiveWorkbook

For i = 1 To .FoundFiles.Count – This can be used to count the number of files that Excel finds and opens.  For each one opened the variable i increments one.
Workbooks.Open (.FoundFiles(i))

Put your code here

Next i – Open next workbook
End If
End With

So using the above piece of code you quickly and easily perform a routine across multiple workbooks.  Just insert the code you want to repeat across all workbooks where it says “Put your code here”.  Hey presto!

2 thoughts on “Automatically open Excel Workbooks in Folder and Run VBA Code

Leave a Reply

Your email address will not be published. Required fields are marked *