Macro 12: Open All Workbooks in a Directory
Here’s a scenario: You’ve written a cool macro that applies some automated processing to each work-
book you open. Now the problem is that you need to go into your directory, open each workbook, run the
macro, save it, close the workbook, and then open the next one. Opening each workbook in a directory is
typically a time-consuming manual process. This macro solves that problem.
How it works
In this macro, we use the Dir function. The Dir function returns a string that represents the name of
the file that matches what you pass to it.
In this code, we use the Dir function to enumerate through all the .xlsx files in a given directory,
capturing each file’s name. Then we open each file, run some code, and finally close the file after
saving.
Sub Macro12()
‘Step 1:Declare your variables
Dim MyFiles As String
‘Step 2: Specify a target directory
MyFiles = Dir(“C:\Temp\*.xlsx”)
Do While MyFiles <> “”
‘Step 3: Open Workbooks one by one
Workbooks.Open “C:\Temp\” & MyFiles
‘run some code here
MsgBox ActiveWorkbook.Name
ActiveWorkbook.Close SaveChanges:=True
‘Step 4: Next File in the Directory
MyFiles = Dir
Loop
End Sub
1. Step 1 declares the MyFiles string variable that will capture each filename that is in the
enumeration.
2. In Step 2, the macro uses the Dir function to specify the directory and file type we are
looking for. Note that the code here is looking for *.xlsx. This means that only .xlsx files will be
looped through. If you are looking for .xls files, you need to change that (along with the
directory you need to search). This macro passes any filename it finds to the MyFiles string
variable.
3. Step 3 opens the file, does some stuff (this is where you would put in any macro code to
perform the desired actions), and then we save and close the file. In this simple example,
we are calling a message box to show each filename as it opens.
4. The last step of the macro loops back to find more files. If there are no more files, the
MyFiles variable will be blank. If that is the case, the loop and macro end.
How to use it
To implement this macro, you can copy and paste it into a standard module:
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. Right-click the project/workbook name in the Project window.
3. Choose Insert➜Module.
4. Type or paste the code in the newly created module.
