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

Method 1 – Using Workbooks.Open Method

  • Open the Visual Basic Editor by going to the Developer tab and clicking on Visual Basic or by pressing Alt+F11.

Embedding VBA Code to Open Workbook and Run Macro

  • Insert a new module by clicking on Module in the Insert tab.

  • Enter the following VBA code in the module:

Sub Show_Opening_Message()
MsgBox "Your Workbook is Opened and VBA Code Ran Successfully"
End Sub
  • Save the code (press Ctrl+S).
  • Create another module and enter the following code:
Sub Open_Workbook_and_Run_Macro()
Workbooks.Open "C:\Users\Asus\Desktop\New_Workbook.xlsm"
Run "Show_Opening_Message"
End Sub
  • Save the code (press Ctrl+S).
  • Close the Visual Basic Editor.
  • Go to the Developer tab and select Macros. 

Embedding VBA Code to Open Workbook and Run Macro

  • A new dialog box called Macro will appear.
  • Choose Open_Workbook_and_Run_Macro, and click Run.

Using Workbooks.Open Method to Open Workbook and Run Macro

  • The chosen workbook has been opened.

VBA Open Workbook and Run Macro

Breakdown of VBA Code

Sub Open_Workbook_and_Run_Macro()

We provide a name for the sub-procedure which is Open_Workbook_and_Run_Macro.

Workbooks.Open "C:\Users\Asus\Desktop\New_Workbook.xlsm"

We use the Workbooks.Open method to open our desired workbook from our desired location.

Run "Show_Opening_Message"
We run our

Show_Opening_Message

macro to show the opening message.
End Sub

We end the sub-procedure of this code.

Read More: Excel VBA to Open Workbook in Background


Method 2 – Utilizing Application.Run Method

  • Open the Visual Basic Editor.

Embedding VBA Code to Open Workbook and Run Macro

  • Insert a new module.

  • Enter the following code:

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
  • Save the code (press Ctrl+S).
  • Close the Visual Basic Editor.
  • Go to the Developer tab and select “Macros. 

Embedding VBA Code to Open Workbook and Run Macro

  • Choose Run_Macro_After_Opening_Workbook, and click Run.

Utilizing Application.Run Method to Open Workbook and Run Macro

  • The desired workbook has been opened.

VBA Open Workbook and Run Macro

Breakdown of VBA Code

Sub Run_Macro_After_Opening_Workbook()

We provide a name for the sub-procedure which is Run_Macro_After_Opening_Workbook.

Dim A As String
A = ActiveWorkbook.Name

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"

We use the Workbooks.Open method to open our desired workbook from our desired location.

Application.Run "'New_Workbook.xlsm'!Show_Opening_Message"

We use the Application.Run method to run the Show_Opening_Message macro from the New_Workbook.xlsm file.

End Sub

We end the sub-procedure of this code.

Read More: How to Open Workbook with Variable Name with Excel VBA


Method 3 – Using Do-While Loop

  • Open the Visual Basic Editor by going to the Developer tab and clicking on Visual Basic or by pressing Alt+F11.

Embedding VBA Code to Open Workbook and Run Macro

  • Insert a new module by clicking on Module in the Insert tab.

  • Enter the following VBA code in the module:

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
  • Save the code (press Ctrl+S).
  • Close the Visual Basic Editor.
  • Go to the Developer tab and select Macros.

Embedding VBA Code to Open Workbook and Run Macro

  • Choose Open_File_and_Run_Code, and click Run.

Using Do-While Loop to Open Workbook and Run Macro

  • The desired workbook has been opened.

VBA Open Workbook and Run Macro

Breakdown of VBA Code

Sub Open_File_and_Run_Code() 
We provide a name for the sub-procedure which is

Open_File_and_Run_Code

. 
 Const S_Path = "C:\Users\Asus\Desktop\" 
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 
We declare two variables

S_Fil

 and 

W_S

.
S_Fil = Dir(S_Path & "*.xl*") 
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") 
We apply the

Do-While

loop. Inside that loop, we set our 

W_S

variable to open our desired workbook. 
We apply the 

Application.Run

 method to run the 

Show_Opening_Message

 macro of that file.
S_Fil = DirLoop 
We use the

Dir

function again and close the loop.
End Sub 
We end the sub-procedure of this code.

Read More: How to Open Workbook as Read-Only with Excel VBA


Method 4 – Applying RunAutoMacros Method

  • Open the Visual Basic Editor.

Embedding VBA Code to Open Workbook and Run Macro

  • Insert a new module.

  • Enter the following code:

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
  • Save the code (press Ctrl+S).
  • Close the Visual Basic Editor.
  • Go to the Developer tab and select Macros.

Embedding VBA Code to Open Workbook and Run Macro

  • Choose Run_Macro_in_Another_File, and click Run.

Applying RunAutoMacros Method to Open Workbook and Run Macro

  • The desired workbook has been opened.

VBA Open Workbook and Run Macro

Breakdown of VBA Code

Sub Run_Macro_in_Another_File() 
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" 
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 
We apply the

Workbooks.Open

 and 

RunAutoMacros

 to open the 

New_Workbbok.xlsm

 file and run the 

Auto_Open

 macro, respectively.
End Sub 
We end the sub-procedure of this code.

Read More: How to Open Workbook from Path Using Excel VBA 


Download Practice Workbook

You can download the practice workbooks from here:


Related Articles

Get FREE Advanced Excel Exercises with Solutions!
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