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 thing 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 of such a technique is we can open another workbook from a different workbook. In this special article, we will show you how to open excel workbook using Workbooks.Open method in detail.
Open a Workbook from Current Workbook
Just imagine a situation where you are working with two workbooks which are already opened. The workbook that you are working is named as “Book 1” and another workbook which 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 WORKBOOKS object.
Inside this, we need to enter the workbook name and its extension.
Then we need to insist on what we need to do with this workbook. Since we need to activate this workbook select “Activate” method.
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.
- 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 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.
Imagine you are trying to open the file named “File 1.xlsx” on your computer. File is saved in so many folders and subfolders. In order to open it through VBA coding follow below steps.
Step 1: Start the subprocedure.
Sub Workbook_Example1() End Sub
Step 2: Inside the VBA subprocedure start Workbooks.Open method.
Step 3: The very first thing we need to mention is file name with its folder path and extension of the file.
For this first, we need to open the exact file location.
Now what you have to do is you need to copy the file path by using folder path.
Step 4: Now copy the link and paste in coding as follows.
Sub Workbook_Example1() Workbooks.Open Filename:="D:\Excel Files\VBA\File1.xlsx" End SubM
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.
Sub Workbook_Example1() Workbooks.Open Filename:="D:\Excel Files\VBA\File1.xlsx" End Sub
Now ignore all the other arguments.
Step 5: Run the code it will open up the workbook named as “File 1.xlsx”.
So, our code has just opened the mentioned workbook in the mentioned folder path.
The best and efficient way of using VBA coding boils down to use of variables while coding. Because as part of large VBA project we may need to open other files in between the coding, so in such situations use of variables 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.
Sub Workbook_Example2() Dim File_Location As String Dim File_Name As String End Sub
For File_Location variable assign the folder path where the file is stored.
Sub Workbook_Example2() Dim File_Location As String Dim File_Name As String File_Location = "D:\Excel Files\VBA\" End Sub
Note: One extra thing we need to do is after pasting the link we need to put backward slash.
Now for File_Name variable, we need to mention file name with its excel extension.
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
Now, these two variables combine to create a full folder path.
Now open Workbooks.Open method again in excel VBA.
For the first argument supply, two variable names with an ampersand (&) sign.
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
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 PASSWORD argument under Workbooks.Open method.
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 –