How to Open a Workbook by Specifying Its Path in Microsoft Excel Using VBA
Today’s tutorial educates how to use a command in VBA to open another workbook just by specifying its path.
Open a Workbook at a Specific Path in Microsoft Excel
VBA allows you to do all tasks with the help of a few commands only. For example, you can easily open a Workbook using its path in VBA. Then, using simple VBA statements, you can add or reference another Workbook within the same Workbook.
The Workbooks.Open() function allows the user to open another workbook by specifying its path as a parameter to the function.
The user can either provide the entire path in the parameter or break it down into different variables and then concatenate them to form an argument to the Workbooks.Open() function.
Let’s discuss the Workbooks.Open() command before moving on to an example.
the Workbooks.Open() Command
The Workbooks.Open() command allows up to 15 parameters in total. The expression for the command is as follows:
Workbooks.Open (FileName, UpdateLinks, ReadOnly, Format, Password, WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter, Editable, Notify, Converter, AddToMru, Local, CorruptLoad)
The hint of all the parameters is given below. To learn more about the parameters, visit the Microsoft documents for the Workbooks.Open() command.
| Parameter | Description |
|---|---|
FileName |
The FileName string contains the workbook’s name to be opened. |
UpdateLinks |
The UpdateLinks specifies how the external links (references) are updated in the workbook that must open. |
ReadOnly |
The ReadOnly parameter opens the workbook in read-only mode. |
Format |
The Format argument specifies the delimiter character if Microsoft Excel opens a text file. |
Password |
The Password argument is passed as a string that contains the password to open a protected workbook. |
WriteResPassword |
The WriteResPassword argument contains the password that would be required to write to a write-reserved workbook. |
IgnoreReadOnlyRecommended |
The IgnoreReadOnlyRecommended argument, if set true, Microsoft Excel does not display the read-only recommended message. It is done if saved the workbook with the Read-Only Recommended option. |
Origin |
The Origin parameter indicates where the file originated if it is a text file. It helps to correctly map the code pages and Carriage Return Line Feed (CR/LF). |
Delimiter |
The Delimiter argument specifies which character to use as the delimiter - to separate substrings. |
Editable |
The Editable takes true as the value to open the add-in so that it is a visible window. Otherwise, the add-in remains hidden. This parameter applies if the file is a Microsoft Excel 4.0 add-in and does not work with Microsoft Excel 5.0 or later. |
Notify |
The Notify is set as true to add the file to the file notification list if it is not available in read-write mode. A read-only version is opened, the notification list polled, and the user is notified when the file becomes available. |
Converter |
The Converter argument is optional. It is the index of the first file converter to try when opening the file. |
AddToMru |
The AddToMru is true if the user wants to add the workbook to the recently used files list. Otherwise, it is set as false. |
Local |
The Local is true if the user wants to save the file against the language of Microsoft Excel. Otherwise, the file is saved against the language of VBA. |
CorruptLoad |
The CorruptLoad takes either of the three arguments: xlNormalLoad, xlRepairFile, or xlExtractData. |
Note: All of the parameters for the
Workbooks.Open()command are optional.
Method 1: Open a Workbook at a Specific Path in Microsoft Excel
Here is the first method using which you can open a workbook in Microsoft Excel by specifying its path:
Workbooks.open("D:\useruser\DataMatrix\F3 L93-L050 16MY\Finance Graphics_1345222.xlsm")
The entire path to the Microsoft Excel workbook is provided in the Workbooks.Open() command. It is one of the methods that you can follow to open another workbook using the VBA commands.
Method 2: Open a Workbook at a Specific Path in Microsoft Excel
The second method is the same as the first; however, the path is broken down into different components for a better understanding and a cleaner code. Here is the code snippet of the second method:
Dim path As String, file As String
path = "D:\useruser\DataMatrix\F3 L93-L050 16MY\"
file = path & "Finance Graphics_1345222.xlsm"
Workbooks.Open(file)
The code in the second method is more readable than the statement in the first method. Although, the second method requires more lines for the code.
So, we have learned that the Workbooks.Open() command in VBA allows the user to open another workbook with just one command. It is essential to specify the path of the desired workbook.
Besides that, the Workbooks.Open() command offers numerous parameters that users can use according to their requirements.
Hello, I am Bilal, a research enthusiast who tends to break and make code from scratch. I dwell deep into the latest issues faced by the developer community and provide answers and different solutions. Apart from that, I am just another normal developer with a laptop, a mug of coffee, some biscuits and a thick spectacle!
GitHub