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.
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.
- Secondly, from the Insert tab, select Module.
- 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 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.
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 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.
- 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.
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 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.
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!
