VBA Workbooks Open. A workbook is an object in Excel VBA. It can make chart sheets, cells and ranges, shapes, and chart objects, among other things. We can open a specific workbook, save it, close it, make a new one, change its properties, and do many other things. In Excel VBA, there are two ways to open a workbook. These are:
- The Workbooks.Open method.
- The Application.GetOpenFilename method.
Syntax
“Workbooks.Open Filename:=”File_Name”
“File Name” is the name of the Workbook file that we want to open. When we need the Workbook’s file name, we need to give the file’s path, name, and extension, such as (.XLSX), (.xlsm), (.XLS), etc.
Workbooks.Open Method
VBA Workbooks.Open is a method for opening an Excel file from another Excel file. The method comprises 15 optional arguments. With these 15 optional arguments, you can set how the Workbooks.Open way work in different ways.
See More: Type Mismatch VBA
Example
Let’s say you want to open a file called “Test File” in Excel. This file is stored on the computer in the D drive.
Follow these steps to open “Test File”:
Step 1: Make a macro in the Workbook.
Step 2: In the Workbook, click on the File button.
Step 3: Hit the Options in the menu.
Step 4: Now, click on the option “Customize Ribbon.”
Step 5: Click on the OK button and then on the Developer tab.
Step 6: The Developer tab now appears in the Excel workbook’s main tab.
Step 7: Click the Developer tab and choose Visual Basic from the list of options.
Step 8: The code window comes up when you click the Visual Basic option.
Step 9: Now, type the code below.
Step 10: Click on the Run button to run the code.
The “Test File” Excel file, saved on the system’s D drive, opens automatically. There is no subfolder to get to the file, so the path given is apparent. The Macro will fail to run if the correct file path is not specified or an incorrect file name is given. Therefore, the file’s name and location should be accurate.
Application.GetOpenFileName Method
The limitations of Workbooks are overcome by using this technique. While the prior approach worked, it had issues whenever the path or file name was modified. You can search for a file on your computer using the Application GetOpenFileName method. The tool makes it easy to track down the stored data. The VBA Workbooks are still required to access the file. It has five arguments.
Example
Follow the steps below to open the saved file:
Step 1: Navigate to the Developer menu and pick the Visual Basic sub-menu.
Step 2: Give a name to your Macro by typing the following into the code box.
Step 3: Write the statement that makes Filename a variable.
Step 4: Open a dialogue window by typing this statement.
Step 5: If the Filename is not equal to false, the next step is to write an If-Then statement in the code that will open the specified Excel file.
Step 6: Hit Run to put the preceding code into effect. It opens up a dialogue box.
- Go to the file’s location on the desktop.
- The D file is finally visible.
- Choose the file and hit the Open button.
Here, the file name and path aren’t needed. We can rapidly find and open a saved file. This method is better than using Workbooks.Open method.
