VBA Workbooks Open
In Excel VBA, Workbook is an object. It performs different types of purposes, such as chart sheets, cells and ranges, shapes, and chart objects.
We can open a specific workbook, save a workbook, close a workbook, create a workbook, change the properties of a workbook, and many more.
There are two methods to open a workbook in Excel VBA, such as:
- The Workbooks.Open method.
- The Application.GetOpenFilename method.
Workbooks.Open Method
VBA Workbooks.Open is a method that is used to open an Excel workbook from another workbook.
The Workbooks.Open method has 15 optional arguments. These 15 optional arguments allow us to define a different feature of how the Workbooks.Open method opens an excel workbook.
Syntax
In VBA, The basic syntax to open a workbook is:
OR
Explanation
- “File_Name” is the file name of the workbook which we want open.
- When requiring the workbook’s file name, then we need to provide the fill path and name of the file with the extension of the files, such as (.XLSX), (.xlsm), (.XLS) and many more.
Example
Suppose we want to open an excel file named “Example File”. This file is saved in the E drive of the computer system.
To open the “Example File” using the Workbooks.Open method, follow the following steps:
Step 1: Create a Macro in Workbook.
Step 2: Click on the File option in the workbook.
Step 3: Click on the Options.
Step 4: Now click on the Customize Ribbon option.
Step 5: Select the Developer tab and click on the OK button.
Step 6: Now, the Developer tab appears in the main tab of the excel workbook.
Step 7: Click on the Developer tab and select the Visual Basic option.
Step 8: After clicking the Visual Basic option, it displays the code window.
Step 9: Now write the following code.
Step 10: Execute the code by clicking on the Run button.
Now, the “Example File” Excel file is automatically open, which is saved in the E drive of the system.
The mentioned path is very clear because there is no subfolder to get the file. If the path of the file is not set correctly or the file name is entered wrong, then the Macro will not work. So the name and path of the file should be correct.
Application.GetOpenFileName Method
This method removes the drawbacks of the Workbooks.Open method. The previous method is facing the problem while changing the path and file name.
But the Application.GetOpenFileName method gives the option to browse in the system. With the help of this feature, we can quickly locate the saved file.
To open the file, we still dependent on the VBA Workbooks.Open method. There are 5 arguments in the Application.GetOpenFileName.
Example
To open the saved file, follow the following steps, such as:
Step 1: Click on the Developer tab and select the Visual Basic option.
Step 2: Write the following code on the code window, which provides a name to the Macro.
Step 3: Write the next statement that declares the Filename as the variable because, in this method, we are not specifying any path or file name.
Step 4: Write this statement, and it will open a dialog box. Now we can browse the folders to go to the path where the file located.
Step 5: Write the If-Then statement in the code which is used when the file name is not equal to false, then it opens the selected Excel file.
Step 6: Now, execute the above code by clicking on the Run button. It displays a dialog box to browse the file.
- Browse to the desktop where the file is saved.
- Now we can see the file in the E
- Select that file and click on the Open
In the above example, we don’t need to provide the file name and the path of the file. We can quickly locate the saved file and select that file which we want to open.
So, this method is more efficient as compared to Workbooks.Open method.
