VBA Workbook Open

Excel VBA Workbooks.Open Method

VBA Workbooks.Open Method is used to open an excel workbook from another workbook.

As far as VBA is concerned, I hope you have seen the magic and wonders VBA can do at your workplace. One of the substantial things about VBA is it doesn’t limit its work in the same workbook. Rather, we can access the workbook which is already not opened. One such technique is we can open another workbook from a different workbook. In this special article, we will show you how to open an excel workbook using Workbooks.Open method in detail.

VBA-Workbook-Open

Open a Workbook from Current Workbook

Just imagine a situation where you are working with two workbooks that are already opened. The workbook that you are working on is named “Book 1” and another workbook that is open but not active is named as “Book 2”.

Since we are working in “Book 1” in order to activate another workbook, “Book 2,” we need to specify the workbook name with its file extension by using the WORKBOOKS object.

Excel VBA Workbook.Open Example 0

Inside this, we need to enter the workbook name and its extension.

Excel VBA Workbook.Open Example 0.1

Then we need to insist on what we need to do with this workbook. Since we need to activate this workbook, select the “Activate” method.

Excel VBA Workbook.Open Example 0.2

So, this code will activate the workbook “Book 2.xlsx” if it is opened.

What if the workbook “Book 2.xlsx” is not opened??? How do you open it or activate it????

This is where our Workbooks.Open method comes into the picture.

Syntax

Excel VBA workbooks.open method

  • File Name: The first argument in this method is we need to specify the workbook name that we are trying to open. Workbook name alone cannot do the job here because excel doesn’t know in which folder your workbook is saved. So we need to supply a full folder path followed by an exact file name with its saved extension.
  • Update Links: When we try to open the workbook, it may have contained some external links from other workbooks. We can supply yes or no here.
  • Read Mode: How do you want to open the workbook?? If you want the file only to be read, then TRUE is the argument.
  • Password: If the targeted or opening workbook contains any password, then in this argument, we need to specify the password that was used while protecting the workbook.

These four arguments are good enough in this method. Do not break your head to understand each and every argument because the need for those remaining arguments may never arise.

Examples

You can download this VBA Workbook Open Excel Template here – VBA Workbook Open Excel Template

Example #1

Imagine you are trying to open the file named “File 1.xlsx” on your computer. The file is saved in so many folders and subfolders. In order to open it through VBA coding, follow the below steps.

Step 1: Start the subprocedure.

Code:

Sub Workbook_Example1()

End Sub

Excel VBA Workbook.Open Example 1.1

Step 2: Inside the VBA subprocedure, start Workbooks.Open method.

Excel VBA Workbook.Open Example 1.2

Step 3: The very first thing we need to mention is to file a name with its folder path and extension of the file.

For this first, we need to open the exact file location.

Excel VBA Workbook.Open Example 1.3

Now what you have to do is you need to copy the file path by using the folder path.

Workbook.Open Example 1.4

Step 4: Now copy the link and paste in coding as follows.

Code:

Sub Workbook_Example1()

Workbooks.Open Filename:="D:\Excel Files\VBA\File1.xlsx"

End SubM

Workbook.Open Example 1.5

So, now we have copied and pasted the file path where it is stored. In this mentioned file path, it may contain many files, so after this, enter backward slash first and then enter the file name with a file extension.

Code:

Sub Workbook_Example1()

Workbooks.Open Filename:="D:\Excel Files\VBA\File1.xlsx"

End Sub

Workbook.Open Example 1.6

Now ignore all the other arguments.

Step 5: Run the code. It will open up the workbook named “File 1.xlsx”.

So, our code has just opened the mentioned workbook in the mentioned folder path.

Example #2

The best and efficient way of using VBA coding boils down to the use of variables while coding. Because as part of a large VBA project, we may need to open other files in between the coding, so in such a situation,ns use of variables is going to play an important role.

The same file can be opened by using variables as well. For this, declare two variables as a string data type.

Code:

Sub Workbook_Example2()

    Dim File_Location As String
    Dim File_Name As String

End Sub

Example 2.1

For the File_Location variable, assign the folder path where the file is stored.

Code:

Sub Workbook_Example2()

    Dim File_Location As String
    Dim File_Name As String

    File_Location = "D:\Excel Files\VBA\"

End Sub

Example 2.2

Note: One extra thing we need to do is after pasting the link, we need to put backward slash.

Now for the File_Name variable, we need to mention the file name with its excel extension.

Code:

Sub Workbook_Example2()

    Dim File_Location As String
    Dim File_Name As String

    File_Location = "D:\Excel Files\VBA\"
    File_Name = "File1.xlsx"

End Sub

Example 2.3

Now, these two variables combine to create a full folder path.

Now open Workbooks.Open method again in excel VBA.

Example 2.4

For the first argument supply, two variable names with an ampersand (&) sign.

Code:

Sub Workbook_Example2()

    Dim File_Location As String
    Dim File_Name As String

    File_Location = "D:\Excel Files\VBA\"
    File_Name = "File1.xlsx"

    Workbooks.Open File_Location & File_Name

End Sub

Example 2.6

So now, we can change the folder path and file name whenever we want to for just variables, so wherever we use variables, it will reflect the changes instantly.

Things to Remember

  • Never ever enter the file location manually because 99% of the time, we make mistakes while typing the location, so copy from the location only.
  • To separate location and file, we need to enter a backward slash (\) manually.
  • If the file is protected by a password, then use the PASSWORD argument under Workbooks.Open method.

Recommended Articles

This has been a guide to VBA WorkBook Open Method. Here we discuss how to open an excel workbook from another workbook using VBA Workbooks.Open method. You can learn more from the following VBA articles –

  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>