VBA Open Workbook

Open Workbook In VBA

VBA Open Workbook helps us open one or more files using the Workbooks.Open method. While writing code, we cannot manually open the desired workbook to perform certain tasks in the middle. So, we write code that opens the workbook using the Workbooks.Open method.

Using VBA coding, we can open other workbooks from specific locations. When we work with an Excel workbook, we will not have the data in the same workbook always; so, we may have to open other workbooks from different folder locations of the computer that we are working on. This can be done with the help of VBA Open Workbook.

Open Workbook In VBA

VBA Open Workbook helps us open one or more files using the Workbooks.open method.
Remember that the complete folder path and file name, along with the file extension, is mandatory while using VBA Open Workbook.
We can open files that are there on our local computer or laptop.
Also, we can open one file, or we can open all the files in a folder.
The password should be exactly the same as the workbook password to open, and it is case-sensitive.

Workbook.Open Syntax

Before we start writing the code to open a specific workbook, let us first learn about the syntax of the Workbook.open method in VBA.

Workbooks.Open(FileName, UpdateLinks, ReadOnly, Format, Password, WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter, Editable, Notify, Converter, AddToMru, Local, CorruptLoad)

File Name – In this argument, we need to mention the workbook name that we are trying to open along with the file extension of the workbook. However, not only the workbook name is sufficient in order to open the workbook; we need to specify the folder where it is saved on our computer or laptop.

Update Links – When we open the workbook, if the workbook contains any of the links from other workbooks, we can specify YES to update the data, or else we can give NO.

Read Only: If you want to open the workbook in read mode, we need to give TRUE here.

Password – If the opening workbook contains a password, then we can give that password in this argument to successfully open the workbook.

It is highly unlikely that we will use all the arguments in the Workbooks.Open method. The above 4 arguments are good enough in most situations.

Steps To Open A Workbook Using VBA

The steps involved in writing a VBA code to open the Excel workbook from the active workbook are listed as follows:

Step 1: Open the Visual Basic Editor window by pressing the Excel shortcut keys ALT + F11. Or we can click the Visual Basic option under the Developer tab.

Step 2: This will open the visual basic editor window like the following.

Step 3: Under the Insert tab, click on Module.

Step 4: This will insert the new module with the coding window to the right side (white space).

Note: The shortcut keys to insert a new module is ALT + I + M.

Step 5: In the coding window, start the subroutine procedure by naming the macro “Open_Workbook”.

Step 6: Use the workbook object by entering the word WORKBOOK.

Step 7: Choose the Workbooks object from the IntelliSense list and enter a dot (.), and choose the Open method from the IntelliSense list.

Step 8: Once the Open method is selected, we need not have to open parenthesis; just press the space button to see the syntax of the OPEN method.

Step 9: The first argument is File Name. So, enter the desired file name and its completed folder path address in double-quotes.

Go to the file folder location.

Once we click on the top bar, it will show the folder path address like the following.

Step 10: Copy this address and enter this in double quotes for the File Name argument of the Workbooks.Open method.

Step 11: Once the folder path is provided, we need to give the desired workbook name along with the file extension of the workbook.

For example, we are trying to open the workbook named Migration Report Tracker, which has the file extension as “xlsx”.

Step 12: Copy this file name along with the file extension.

After the folder path, enter a backslash (\) and then paste the file name.

Let’s run this code by hitting on the green run button in the Visual Basic Editor window.

When we run this code, we can observe our screen flickering a bit. Then, our desired workbook will be opened.

Examples

Example #1 – Opening A Password Protected Workbook

There are chances where users may have protected the workbook with a password so that other users do not open it. Hence, whenever we write a VBA code to open any password-protected workbook, we need to provide the password to open them.

Sub Open_Workbook_Password()

Workbooks.Open "C:\Users\User\Downloads\New folder\Code Function.xlsx", Password:="Hello1234"

End Sub

Use Variables: The above code looks a little clumsy and lengthy. We can refer to the following code to make it more readable and understandable.

Part #1 – We have defined two variables with the string data type.

Part #2 – For the first variable, we are assigning the file folder name along with the file name and extension.

Part #3 – For the second variable, we are assigning the password of the desired workbook.

Part #4 – Next, we are using the Workbooks.Open method. However, instead of giving the direct value, we have provided the variable names for which desired values are already assigned.

Example #2 – Opening A Workbook As Read Only

If you want to open the workbook in reading mode, we will not get access to edit that workbook. The following code will open the workbook in read mode.

Here, we have used the “ReadOnly” argument of the Workbook.Open method and set this argument value to TRUE.

When we run this code, it will open the given workbook from the given folder, but it will be in read mode.

As we can see, it says “Read-Only”.

When we change something and try to save it, we will get the following alert message.

The warning message says, “We can’t save ‘Migration Reports Tracker.xlsx’ because the file is read-only”.

To save changes, either we need to save them as a different workbook or in a different folder location.

Example #3 – Opening All The Workbooks From A Folder

We may have multiple files in a single folder. To open all the workbooks in a folder, we may have to write the same code as many times as the workbook count in that folder.

However, we can minimize the code by using the loops. The following code will help us open all the workbooks in a folder.

The above code will loop through the folder path and open all the workbooks with the extension “xls”.

To use the above code, we need to change the folder path location for the variable “Folder_Path”.

Important Things To Note

We can access any of the arguments in the Workbook.open method by entering the argument name followed by colon (:) and equal sign. For example, if we want to give input for the argument read only, we can access this argument like this “ReadOnly:=”.
To combine the folder path with the file name, we need to enter the backslash (\).
Always enter the folder inside the code and preferably with the variable to debug it later on if an error occurs.

Frequently Asked Questions (FAQs)

1. What is the use of VBA Open Workbook in Excel?

We can open any workbooks in the middle of code to get some inputs or perform some set of actions in the opened workbook without much of trouble or manual work.

2. Why does VBA Open Workbook don’t update links?

By default, VBA open workbook method will not update external links to get the updated data. We need to set the argument Update Links to TRUE to update the links on opening the workbook.

3. How do I open another workbook in Excel VBA?

To open another workbook inside the VBA code, we need to use the Workbooks.Open method in VBA.

4. How to close an open workbook in VBA?

Whenever the new workbook opens it becomes an active workbook. So, to close it, we can use the code ActiveWorkbook.Close.

Download Template

This article must be helpful to understand the VBA Open Workbook, with its formula and examples. You can download the template here to use it instantly.

VBA-Open-Workbook Download