How to Apply VBA to Open and Activate Excel Workbook

Get FREE Advanced Excel Exercises with Solutions!

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


Open and Activate Workbook with VBA: 3 Handy Approaches

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

Read More: Activate Another Workbook with VBA in Excel


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

Read More: Excel VBA to Activate Workbook with Partial Name


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


Download Practice Workbook

You can download the Excel file from the link below.


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. Thanks for reading, keep excelling!

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.
Rafiul Haq
Rafiul Haq

Rafiul Haq worked as an Excel and VBA Content Developer in Exceldemy for over two years and published almost 200 articles for the website. He is passionate about exploring new aspects of Excel and VBA. He received his Bachelor of Science in Mechanical and Production Engineering (MPE) from the Islamic University of Technology. Rafiul furthered his education by obtaining an MBA in Finance from the Institute of Business Administration (IBA) at the University of Dhaka. Apart from creating... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo