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.


Download Practice Workbook

Download this practice workbook for practice while you are reading this article.


4 Suitable Examples to Open Workbook and Run Macro Using VBA

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: How to Open Folder and Select File Using Excel VBA (4 Examples)


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 Browse for File Path Using Excel VBA (3 Examples)


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 (4 Examples)


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.

Don’t forget to check our website, ExcelDemy, for several Excel-related problems and solutions. Keep learning new methods and keep growing!


Related Articles

Soumik Dutta

Soumik Dutta

Hi! I am Soumik. I have completed my Bachelor of Science in Naval Architecture and Marine Engineering. I have found my calling in Machine Learning and Data Science, and in pursuing so, I have realized the importance of Data Analysis. Excel is one excellent tool to fulfill the purpose. I am always trying to learn every day about different features of Excel and trying to share here what I am learning.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo