How to Open Workbook and Run Macro Using VBA (4 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.

Embedding VBA Code to Open Workbook and Run Macro

  • 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.

Embedding VBA Code to Open Workbook and Run Macro

  • A new dialog box called Macro will appear.
  • Select Open_Workbook_and_Run_Macro and click on the Run button to run this code.

Using Workbooks.Open Method to Open Workbook and Run Macro

  • You will notice that our desired workbook is opened. Moreover, you will see the message box also.

VBA Open Workbook and Run Macro

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.

Embedding VBA Code to Open Workbook and Run Macro

  • 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.

Embedding VBA Code to Open Workbook and Run Macro

  • 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.

Utilizing Application.Run Method to Open Workbook and Run Macro

  • You will see that our desired workbook is opened. Moreover, you will see the message box also.

VBA Open Workbook and Run Macro

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.

Embedding VBA Code to Open Workbook and Run Macro

  • 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.

Embedding VBA Code to Open Workbook and Run Macro

  • 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.

Using Do-While Loop to Open Workbook and Run Macro

  • You will notice that our desired workbook is opened. Moreover, you will see the message box also.

VBA Open Workbook and Run Macro

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.

Embedding VBA Code to Open Workbook and Run Macro

  • 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.

Embedding VBA Code to Open Workbook and Run Macro

  • 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.

Applying RunAutoMacros Method to Open Workbook and Run Macro

  • You will see that our desired workbook is opened. Moreover, you will see the message box also.

VBA Open Workbook and Run Macro

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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Soumik Dutta
Soumik Dutta

Soumik Dutta, having earned a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, plays a key role as an Excel & VBA Content Developer at ExcelDemy. Driven by a profound passion for research and innovation, he actively immerses himself in Excel. In his role, Soumik not only skillfully addresses complex challenges but also demonstrates enthusiasm and expertise in gracefully navigating tough situations, underscoring his unwavering commitment to consistently deliver exceptional, high-quality content that... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo