Welcome back Excellers to another #Excel #MacroMonday blog post in my 202 series. Today I will show you how to write a small VBA Excel Macro that will open all files or workbooks in a specific directory all at once.

This macro is very useful if you have a directly of files that you work with regularly and always need to to go into them one by one. This Macro can help you save time by opening them all at once. For example those regional sales reports. Those multiple products reports that you work on altogether. This will help by opening them all at once for you.

On the first Wednesday of EVERY month, I share three FREE Excel tips. Sign up to receive these tips direct to your mailbox. Do join thousands of other subscribers. In short, we like Excel. Do you?.

Prepare To Write The Macro

First, open the Visual Basic Editor.  There are two ways to do this.  Hit ALT +F11 or selecting the Developer Tab | Code Group | Visual Basic.  Both methods have the same result.   

If you cannot see the Developer Tab you may need to enable it. Check out my very short YouTube video for instructions. I will show you how to enable the developer tab in just a few minutes.

You then have a choice. You can either create a module to store your code either in your Personal Macro Workbook or in your current workbook.  What’s the difference?.

If you save the macro in your Personal Macro workbook it will be available for use in any of my Excel workbooks.  If you store it in the current workbook then use is restricted to that workbook.

Learn More About Your Personal Macro Workbook (PMW)

Therefore, if you want to read more about your Excel PMW then check out my blog posts below.

Creating and Updating Your Personal Macro Workbook

Create A Shortcut To Your Personal Excel Macro Workbook

Why Is My Personal Macro Workbook Not Loading Automatically?

To start with, I want to reuse this handy macro over and over again. So, I will insert a new module into my Personal Macro Workbook. It is usable over and over again .

Starting The Macro.

After the new module is inserted, type Sub then the name of the Excel macro. In this example, I have named my macro OpenSalesReports.

[stextbox id=’info’]
Sub OpenSalesReports
End Sub
[/stextbox]

Declare Variables.

Next, any variables need to be declared. This simply means that Excel allocates some memory to store these values. In this case MyFiles are declared. This means that Excel will capture each file name in the directory we name in the code.

[stextbox id=’info’]
Dim Myfiles As String
[/stextbox]

Use Dir Function.

The next part of the code uses the Dir Function to instruct Excel as the location of the directly and also any file types we want to open. Remember we may want to open both Excel and work documents. Not just Excel.

[stextbox id=’info’]
Myfiles = Dir(“C:\Temp\*.xlsx”)
[/stextbox]

Loop Through All Of The Files In the Folder.

We now use the Do While Loop to loop though all of the .xlsx or Excel workbooks. We simply open all of the files until there are non left to open.

[stextbox id=’info’]
Do While Myfiles <> “”
Workbooks.Open “C:\Temp\” & Myfiles
Myfiles = Dir
Loop

[/stextbox]

This piece of code simply opens all of the workbooks in a directory. This is exactly what is needed for this procedure, so the files can be worked on. You can see that by opening the all of the files some extra code could be added here to update the calculations or update the dates for example The files could then be saved and closed. Do you know how to do this?. Have a go and amend the code for yourself. Why not share in the comments below?.

Ending The Macro.

Once all of the workbooks are opened, the code ends.

[stextbox id=’info’]
End Sub
[/stextbox]