Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Apply VBA to Open and Activate Excel Workbook

We can use Excel to solve various problems. Moreover, using Excel VBA is faster for performing many mundane tasks. In this article, we will show you how to apply open a workbook and activate it using VBA macros.

The following animated image shows the VBA code to open a file and activate another file.

Opening and Activating a Workbook Using VBA


Download Practice Workbook

You can download the Excel file from the link below.


3 Handy Approaches to Apply VBA to Open and Activate Workbook

There will be three simple VBA codes to open and activate workbooks. First, we will show you how to open a specific file and activate another workbook. In order to open and activate workbooks with VBA, we will next use the file picker dialog box. Lastly, we will open three files and activate a single workbook. Moreover, there will be a folder with three files (named 1, 2, and 3) for the demonstration.


1. Applying VBA to Open and Activate an Excel Workbook

In this method, before starting, we need to enable the Developer tab in Excel. Then, we will type a macro code to open a workbook and activate it.

Steps:

  • Firstly, from the Developer tab, select Visual Basic. Alternatively, you can press Alt+F11.
  • So, this will bring up the VBA window.

Developer tab of Excel

  • Secondly, from the Insert tab, select Module.

Insert Module option

  • Thirdly, type the following code in the window.
Option Explicit
Sub OpenWorkbook()
Workbooks.Open ("C:\Users\Rafiul Haq\Desktop\Exceldemy\55-0177\test\1.xlsm")
Workbooks("Open and Activate Wb.xlsm").Activate
End Sub

VBA code for activate and open a workbook

VBA Code Breakdown

Option Explicit
  • To begin with, we are making it mandatory to declare all the variables.
Sub OpenWorkbook()
  • Then, we give our Sub Procedure name OpenWorkbook.
Workbooks.Open ("C:\Users\Rafiul Haq\Desktop\Exceldemy\55-0177\test\1.xlsm")
  • After that, we open the “1.xlsm” workbook.
Workbooks("Open and Activate Wb.xlsm").Activate
  • Then, we activate the original workbook.
End Sub
  • Lastly, we end the Sub Procedure.
  • Then, click inside the code and press F5.
  • So, this will execute the code.
  • Now, press Alt+Q to close the VBA window.
  • After that, we will see that “1.xlsm” is opened and our original file is active. This concludes the first method.

Output of the first method


2. Open and Activate a Workbook Using File Picker

We will use the file picker dialog box in this section. There are three files in the folder, and we will open and activate the second file, named “2.xlsm”. Without further ado, let us show you the process.

Steps:

  • Firstly, press Alt+F11 and this will bring up the VBA window.
  • Secondly, from the Insert tab, select Module and type the following code.
Option Explicit
Sub Open_Activate_with_FileDialog()
Dim Wb_Name As String
Dim Our_Workbook As Workbook
Wb_Name = Application.GetOpenFilename(, , , , False)
Set Our_Workbook = Workbooks.Open(Wb_Name)
Our_Workbook.Activate
End Sub

VBA code to open and activate workbook using File Picker

VBA Code Breakdown

Option Explicit
  • To begin with, we are making it mandatory to declare all the variables.
Sub Open_Activate_with_FileDialog()
  • Then, we give our Sub Procedure name Open_Activate_with_FileDialog.
Dim Wb_Name As String
Dim Our_Workbook As Workbook
  • After that, we declare the variable types.
Wb_Name = Application.GetOpenFilename(, , , , False)
  • With this, we open the file picker dialog box. This will ask the user to pick a line.
  • Moreover, using the false parameter we disable multiple file selection.
Our_Workbook.Activate
  • Then, we activate the selected workbook.
End Sub
  • Lastly, we end the Sub Procedure.
  • Then, click inside the code and press F5.
  • So, this will execute the code.
  • Now, the file picker dialog box will appear.
  • Then, navigate to the file location and select your file.
  • After that, press Open.

File Picker dialog box

  • Now, press Alt+Q to close the VBA window.
  • After that, we will see that the workbook “2.xlsm” is open and active. This concludes the second method.

Output of the second method


3. Open Multiple Files and Activate a Workbook in VBA

In this last method, we will open three files and then activate a single file using the VBA code. There will be a Do While loop to go through all the files inside a folder.

Steps:

  • Firstly, press Alt+F11 and this will bring up the VBA window.
  • Secondly, from the Insert tab, select Module and type the following code.
Option Explicit
Sub Open_Multi_Files_and_Activate_One()
Dim Files_in_Folder As String, Folder_Path As String
Folder_Path = "C:\Users\Rafiul Haq\Desktop\Exceldemy\55-0177\test"
Files_in_Folder = Dir(Folder_Path & "\*.xlsm")
Do While Files_in_Folder <> vbNullString
Workbooks.Open (Folder_Path & "\" & Files_in_Folder)
Files_in_Folder = Dir()
Loop
Workbooks("3.xlsm").Activate
End Sub

VBA code to open multiple files and activate a single workbook

VBA Code Breakdown

Option Explicit
  • To begin with, we are making it mandatory to declare all the variables.
Sub Open_Multi_Files_and_Activate_One()
  • Then, we give our Sub Procedure name Open_Multi_Files_and_Activate_One.
Dim Files_in_Folder As String, Folder_Path As String
  • After that, we declare the variable types.
Folder_Path = "C:\Users\Rafiul Haq\Desktop\Exceldemy\55-0177\test"
Files_in_Folder = Dir(Folder_Path & "\*.xlsm")
  • Then, we specify the folder path.
Do While Files_in_Folder <> vbNullString
Workbooks.Open (Folder_Path & "\" & Files_in_Folder)
Files_in_Folder = Dir()
Loop
  • After that, there is a Do While loop to go through all the files and open them.
Workbooks("3.xlsm").Activate
  • Then, we activate the “3.xlsm” workbook.
End Sub
  • Lastly, we end the Sub Procedure.
  • Then, click inside the code and press F5.
  • So, this will execute the code.
  • Now, press Alt+Q to close the VBA window.
  • After that, we will see that “3.xlsm” is open and active. Thus, this concludes the final method.

Output of the third method


Conclusion

We have shown you three easy methods to apply VBA to open and activate the workbook in Excel. Please leave a comment below if you have any questions or concerns about these techniques. However, remember that our website implements comment moderation. Therefore, your comments may not be instantly visible. So, have a little bit of patience, and we will solve your query as soon as possible. Moreover, you can visit our site, ExcelDemy for more Excel-related articles. Thanks for reading, keep excelling!

Rafiul Haq

Rafiul Haq

Hello! This is Rafiul. I have an engineering degree and an MBA (finance) degree. I am passionate about all things related to data, and MS Excel is my favorite application. I want to make people's lives easier by writing easy-to-follow and in-depth Excel and finance related guides here at ExcelDemy.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo