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.

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

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

• The chosen workbook has been opened.

Sub Open_Workbook_and_Run_Macro()

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

Run "Show_Opening_Message"

We run our

macro to show the opening message.

End Sub

Read More: Excel VBA to Open Workbook in Background

Method 2 – Utilizing Application.Run Method

• Open the Visual Basic Editor.

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

• Choose Run_Macro_After_Opening_Workbook, and click Run.

• The desired workbook has been opened.

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

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.

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

• Choose Open_File_and_Run_Code, and click Run.

• The desired workbook has been opened.

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

. 
 Const S_Path = "C:\Users\Asus\Desktop\" 
We declare a constant entitled

 which is the file location of our desired file.
Dim S_Fil As String Dim W_S As Workbook 
We declare two variables

 and 

.
S_Fil = Dir(S_Path & "*.xl*") 
We use the

 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

loop. Inside that loop, we set our 

variable to open our desired workbook. 
We apply the 

 method to run the 

 macro of that file.

S_Fil = DirLoop 
We use the

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.

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

• Choose Run_Macro_in_Another_File, and click Run.

• The desired workbook has been opened.

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

.

Dim A As String
A = "C:\Users\Asus\Desktop\New_Workbook.xlsm" 
We declare our variable

, whose type is a 

Besides that, we assign our active workbook name to that variable.

Workbooks.Open(A).RunAutoMacros xlAutoOpen 
We apply the

 and 

 to open the 

 file and run the 

 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

• 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