Sometimes, we require to open a workbook through VBA code and run a macro from that file. We can do this job using Excel VBA in several ways. In this article, we will demonstrate four examples to open a workbook and run the macro using Excel VBA. If you are also curious about it, download our practice workbook and follow us.
Open Workbook and Run Macro Using VBA: 4 Suitable Examples
To demonstrate the examples, we consider a simple workbook entitled New_Workbook.xlsm. Here, we input a VBA code to show a message while our VBA code will open that worksheet.
1. Using Workbooks.Open Method
We will write a VBA code using the Workbook.Open method to open a workbook and run the macros. In addition, we have to keep the message opening code in the New_Workbook.xlsm in our main file from where we will run our VBA code. The steps to complete this example are given below:
📌 Steps:
- First of all, go to the Developer tab and click on Visual Basic. If you don’t have that, you have to enable the Developer tab. Or You can also press ‘Alt+F11’ to open the Visual Basic Editor.
- A dialog box will appear
- Now, in the Insert tab on that box, click on Module.
- After that, write down the following visual code in that empty editor box.
Sub Show_Opening_Message()
MsgBox "Your Workbook is Opened and VBA Code Ran Successfully"
End Sub- Press ‘Ctrl+S’ to save the code.
- Again, in the Insert tab on that box, click on Module to create a new module.
- Then, write down the following visual code in that empty editor box.
Sub Open_Workbook_and_Run_Macro()
Workbooks.Open "C:\Users\Asus\Desktop\New_Workbook.xlsm"
Run "Show_Opening_Message"
End Sub- Press ‘Ctrl+S’ to save the code in that module.
- Finally, close the Editor tab.
- Then, select the Macros option from the Code group in the Developer tab.
- A new dialog box called Macro will appear.
- Select Open_Workbook_and_Run_Macro and click on the Run button to run this code.
- You will notice that our desired workbook is opened. Moreover, you will see the message box also.
Thus, we can say that our VBA code worked perfectly, and we were able to open a workbook and run the macro using Excel VBA.
🔎 Breakdown of VBA Code
Sub Open_Workbook_and_Run_Macro()👉 First, we provide a name for the sub-procedure which is Open_Workbook_and_Run_Macro.
Workbooks.Open "C:\Users\Asus\Desktop\New_Workbook.xlsm"👉 Then, we use the Workbooks.Open method to open our desired workbook from our desired location.
Run "Show_Opening_Message"👉 After that, we run our Show_Opening_Message macro to show the opening message.
End Sub👉 Finally, we end the sub-procedure of this code.
Read More: Excel VBA to Open Workbook in Background
2. Utilizing Application.Run Method
In this example, we are going to use the Application.Run method to open the workbook and run a macro through Excel VBA. The steps to complete this example are given as follows:
📌 Steps:
- First, go to the Developer tab and click on Visual Basic. If you don’t have that, you have to enable the Developer tab. Or You can also press ‘Alt+F11’ to open the Visual Basic Editor.
- A dialog box will appear
- After that, in the Insert tab on that box, click on the Module option.
- Now, write down the following visual code in that empty editor box.
Sub Run_Macro_After_Opening_Workbook()
Dim A As String
A = ActiveWorkbook.Name
Workbooks.Open Filename:="C:\Users\Asus\Desktop\New_Workbook.xlsm"
Application.Run "'New_Workbook.xlsm'!Show_Opening_Message"
End Sub- Then, press ‘Ctrl+S’ to save the code in that module.
- Finally, close the Editor tab.
- Next, select the Macros option from the Code group in the Developer tab.
- A new dialog box called Macro will appear.
- Now, select Run_Macro_After_Opening_Workbook and click on the Run button to run this code.
- You will see that our desired workbook is opened. Moreover, you will see the message box also.
Therefore, we can say that our VBA code worked precisely, and we were able to open a workbook and run the macro using Excel VBA.
🔎 Breakdown of VBA Code
Sub Run_Macro_After_Opening_Workbook()👉 First, we provide a name for the sub-procedure which is Run_Macro_After_Opening_Workbook.
Dim A As String
A = ActiveWorkbook.Name👉 Then, we declare our variable A, whose type is a string. Besides that, we assign our active workbook name to that variable.
Workbooks.Open Filename:="C:\Users\Asus\Desktop\New_Workbook.xlsm"👉 After that, we use the Workbooks.Open method to open our desired workbook from our desired location.
Application.Run "'New_Workbook.xlsm'!Show_Opening_Message"👉 Moreover, we use the Application.Run method to run the Show_Opening_Message macro from the New_Workbook.xlsm file.
End Sub👉 Finally, we end the sub-procedure of this code.
Read More: How to Open Workbook with Variable Name with Excel VBA
3. Using Do-While Loop
In the following example, we will use the Do-While loop to open the workbook and run a macro through Excel VBA. The procedure is explained below step-by-step::
📌 Steps:
- At first, go to the Developer tab and click on Visual Basic. If you don’t have that, you have to enable the Developer tab. Or You can also press ‘Alt+F11’ to open the Visual Basic Editor.
- A dialog box will appear
- Now, in the Insert tab on that box, click on the Module option.
- Then, write down the following visual code in that empty editor box.
Sub Open_File_and_Run_Code()
Const S_Path = "C:\Users\Asus\Desktop\"
Dim S_Fil As String
Dim W_S As Workbook
S_Fil = Dir(S_Path & "*.xl*")
Do While S_Fil <> ""
Set W_S = Workbooks.Open(S_Path & S_Fil)
Application.Run ("'" + S_Fil + "'!Show_Opening_Message")
S_Fil = Dir
Loop
End Sub- Press ‘Ctrl+S’ to save the code in that module.
- Finally, close the Editor tab.
- After that, select the Macros option from the Code group in the Developer tab.
- A new dialog box called Macro will appear.
- Next, select Open_File_and_Run_Code and click on the Run button to run this code.
- You will notice that our desired workbook is opened. Moreover, you will see the message box also.
Hence, we can say that our VBA code worked effectively, and we were able to open a workbook and run the macro using Excel VBA.
🔎 Breakdown of VBA Code
Sub Open_File_and_Run_Code()👉 First, we provide a name for the sub-procedure which is Open_File_and_Run_Code.
Const S_Path = "C:\Users\Asus\Desktop\"👉 Then, we declare a constant entitled S_Path, which is the file location of our desired file.
Dim S_Fil As String
Dim W_S As Workbook👉 Besides that, we declare two variables S_Fil and W_S.
S_Fil = Dir(S_Path & "*.xl*")👉 Next, we use the Dir function, to get our file name with the file location.
Do While S_Fil <> ""
Set W_S = Workbooks.Open(S_Path & S_Fil)
Application.Run ("'" + S_Fil + "'!Show_Opening_Message")👉 After that, we apply the Do-While loop. Inside that loop, we set our W_S variable to open our desired workbook. Besides it, we apply the Application.Run method to run the Show_Opening_Message macro of that file.
S_Fil = Dir
Loop👉 In the end, we use the Dir function again and close the loop.
End Sub👉 Finally, we end the sub-procedure of this code.
Read More: How to Open Workbook as Read-Only with Excel VBA
4. Applying RunAutoMacros Method
In the last example, we are going to use the RunAutoMacros method to open the workbook and run a macro using Excel VBA. For this example, the sub name of our desired running code must have to be Auto_Open. The procedure is described below step-by-step:
📌 Steps:
- Firstly, go to the Developer tab and click on Visual Basic. If you don’t have that, you have to enable the Developer tab. Or You can also press ‘Alt+F11’ to open the Visual Basic Editor.
- A dialog box will appear
- Afterward, in the Insert tab on that box, click on the Module option.
- Then, write down the following visual code in that empty editor box.
Sub Run_Macro_in_Another_File()
Dim A As String
A = "C:\Users\Asus\Desktop\New_Workbook.xlsm"
Workbooks.Open(A).RunAutoMacros xlAutoOpen
End Sub- Next, press ‘Ctrl+S’ to save the code in that module.
- Finally, close the Editor tab.
- After that, select the Macros option from the Code group in the Developer tab.
- A new dialog box called Macro will appear.
- Select the Run_Macro_in_Another_File option.
- At last, click on the Run button to run this code.
- You will see that our desired workbook is opened. Moreover, you will see the message box also.
Finally, we can say that our VBA code worked successfully, and we were able to open a workbook and run the macro using Excel VBA.
🔎 Breakdown of VBA Code
Sub Run_Macro_in_Another_File()👉 First, we provide a name for the sub-procedure which is Run_Macro_in_Another_File.
Dim A As String
A = "C:\Users\Asus\Desktop\New_Workbook.xlsm"👉 Then, we declare our variable A, whose type is a string. Besides that, we assign our active workbook name to that variable.
Workbooks.Open(A).RunAutoMacros xlAutoOpen👉 After that, we apply the Workbooks.Open and RunAutoMacros to open the New_Workbbok.xlsm file and run the Auto_Open macro, respectively.
End Sub👉 Finally, we end the sub-procedure of this code.
Read More: How to Open Workbook from Path Using Excel VBAÂ
Download Practice Workbook
Download this practice workbook for practice while you are reading this article.
Conclusion
That’s the end of this article. I hope that this article will be helpful for you and you will be able to open a workbook and run the macro using Excel VBA. Please share any further queries or recommendations with us in the comments section below if you have any further questions or recommendations.
Related Articles
- Excel VBA to Open Workbook from Path in Cell
- How to Browse for File Path Using Excel VBA
- How to Open Folder and Select File Using Excel VBA
- How to Open File Dialog Default Folder with Excel VBA
