This is made possible by the Workbooks.Open method, which takes the file location as the argument and opens that Excel file. You can do a lot more with the Workbooks.Open method, such as opening all the files in a given folder, opening files as read-only, opening files and then saving them with a different name, etc. In this tutorial, I will show you how to use VBA to open Excel files and all the amazing things you can do with it.
Workbooks.Open Method
In Excel VBA, you can use Workbooks.Open method to open an Excel file, where you need to specify the file path of the Excel workbook that you want to open. Below is the syntax of the Workbooks.Open method While the Workbooks.Open method can use 15 arguments, in most cases, we only need to use two or three of them. I will show you examples that will make it clearer how to use these arguments.
Open Workbook By Specifying the File Path
Let’s first look at how to open an Excel file where you know the entire file path. Below is the VBA code that would open an Excel file named Example.xlsx in the folder called Project on my system: Note that the part of the Excel file needs to be in double quotes, and you need to specify the entire file path including the name of the file and the extension. In case Excel is not able to find the file at the location that you specified, it will show you an error as shown below.
Open Multiple Excel Files Together
If you want to open multiple Excel files using one single subroutine, you can do that as well (by using multiple Workbooks.Open methods with the file paths) Below I have a VBA code that would open three Excel files, where I need to specify the path for each file in separate Workbooks.Open method.
Open All Excel Workbooks in a Folder
If you have multiple Excel files in a folder, and you want to open all the files in that folder in one go, you can do that easily using the Workbooks.Open method along with the simple Do While loop. Below I have code where I have specified the folder path, and when this code is executed, it goes through each Excel file in the folder and opens it.
Open Excel Files Based on the Name
If you only want to open specific Excel files in a folder based on its name, you can modify the above VBA code to do that. Below I have a VBA code where it would open only those Excel files in the specified folders that have the word “Sales” in the name of the Excel file. In the above code, I have used Dir(FolderPath & “Sales” & “.xls*”) as the FilePath, where the word Sales is wrapped in asterisks (*). This ensures that only the files with the word ‘Sales’ in the name would get opened, and the rest would be ignored.
Opening a Password Protected Excel Workbook
You can also open password-protected workbooks by specifying the password in the VBA code. Below I have a VBA code that would open a password-protected Excel file, where the password is Excel123
Open Excel File By Showing the Open Dialog Box
In the examples covered so far, I have specified the file name or the path of the files that need to be opened in the VBA code itself. However, you can use VBA to open the Open File dialog box, where the user can navigate to the file and select it to open it. Below is the VBA code that would show the Open dialog box, where the user can select the file and open it. I have used On Error Resume Next in case the user decides to close the Open dialog box without selecting any file. If this statement is not used, it will show an error. In this short tutorial, I showed you a couple of different ways you can use to open workbooks using VBA in Excel. While you may not use these codes to simply open an Excel file, the concept could be quite useful when working on bigger VBA projects where you need to open Excel files as part of the code Other Excel tutorials you may also like:
Save As Shortcuts in Excel (Quick and Easy)How to Automatically Open Specific Excel Files on StartupMicrosoft Excel Won’t Open – How to Fix it! (6 Possible Solutions)Using Workbook Object in Excel VBA (Open, Close, Save, Set)Working with Cells and Ranges in Excel VBA (Select, Copy, Move, Edit)Working with Worksheets using Excel VBA (Explained with Examples)
