How to Open Excel Files Using VBA (Examples)

We can easily open an Excel file using Excel VBA by applying the Workbooks.Open method and the path of the file.

The Workbooks.Open method has one required argument of Filename and fourteen optional arguments.

In most cases, we may need to use only one or two optional arguments in addition to the required argument.

This tutorial will look at five examples of how to open Excel files using Excel VBA.

Table of Contents

Example #1: How to Open a Single Excel File Using VBA

We will use the Example 1.xlsx Excel files in the Excel Examples folder on the local hard drive to show how we can open a single Excel file using Excel VBA.

We use the steps below:

Click the File tab and locate the folder containing the file. You can see the file listed in the Open dialog box. Right-click the file and choose Properties on the shortcut menu that appears.

Click the Security tab on the Properties dialog box, , select the file’s path next to the Object name, press Ctrl + C to copy it, and click OK.

Click the Cancel button on the Open dialog box to close it.
Click the Developer tab, and click Visual Basic to open the Visual Basic Editor.

Enter the following statement in the Immediate window. The window is at the bottom of the Visual Basic Editor. The file’s path we copied earlier is on the Windows clipboard, so you need to press Ctrl + V to paste it in between the quotation marks:

Workbooks.Open("D:\Local Disk D\Excel Examples\Example 1.xlsx")

Note: We can use the Immediate window to run the code without creating a module. If you do not see the Immediate window, activate it by pressing Ctrl + G.

With the cursor blinking at the end of the statement, press Enter to run the code.

The code opens the Example 1.xlsx file. You can see the workbook’s window on the taskbar:

Example #2: How to Open Multiple Excel Files Using VBA

We cannot use the Immediate window to open multiple files at once because it can only allow us to run one statement at a time.

We have to create a sub-procedure in a module to open multiple Excel files.

We will use the Example 1.xlsx and Example 2.xlsx files in the Excel Examples folder on the local hard drive to show how we can open multiple Excel files using Excel VBA.

We use the following steps:

Get the paths of the two Excel files as explained previously and paste them into Notepad to access them easily.
Open the Visual Basic Editor, as explained previously.
Open the Insert menu and select the Module option:

Enter the following sub-procedure in the module. Remember to copy and paste the files’ paths from the Notepad we used earlier:

'Code developed by Steve Scott from https://spreadsheetplanet.com
Sub OpenManyWorkbooks()
    Workbooks.Open ("D:\Local Disk D\Excel Examples\Example 1.xlsx")
    Workbooks.Open ("D:\Local Disk D\Excel Examples\Example 2.xlsx")
End Sub

Click somewhere in the code and then press F5 to run the code.

The code opens both Excel workbook files. We can see their windows on the taskbar.

Example #3: How to Open All Workbooks in a Folder Using VBA

If we have two or three workbook files in a folder, we can use the previous method to open all the files in the folder without much of a problem.

But if we have tens or more workbooks in a folder, that method becomes inefficient.

This example shows a more efficient method of opening all files in a folder. The technique uses the folder path, the file path, and a Do Until Loop construct.

We use the five workbooks in the Excel Examples folder to illustrate how to open all the workbook files in a folder using Excel VBA.

Notice that the Example 5 workbook has a .xlsm extension meaning it is a Macro-Enabled Workbook.

We use the following steps:

Open the Visual Basic Editor.
Insert a new module in the Visual Basic Editor.
Enter the following sub-procedure in the module:

'Code developed by Steve Scott from https://spreadsheetplanet.com
Sub OpenAllFilesInFolder()
    Dim wkbk As Workbook
    Dim PathOfFolder As String
    Dim PathOfFile As String
        PathOfFolder = "D:\Local Disk D\Excel Examples\"
        PathOfFile = Dir(PathOfFolder & "*.xls*")
        Do Until PathOfFile = ""
            Set wkbk = Workbooks.Open(PathOfFolder & PathOfFile)
           PathOfFile = Dir
        Loop
End Sub

Click anywhere in the code and then press F5 to run the code.

We can observe the code opening all the workbooks in the folder one after the other. We can see the windows of the workbooks on the taskbar.

all excel files in the folder get opened

Note: We have used PathOfFile = Dir(PathOfFolder & “*.xls*”) as the file path where the extension .xls is sandwiched between two asterisks wildcard characters. This ensures that both the regular and Macro-Enabled Workbooks are opened.

Example #4: Open Specific Workbooks Using VBA

Suppose our Excel Examples folder had files with the word “Budget” in their filenames.

To open only those workbook files, we would have to tweak our sub-procedure in the previous example as below:

'Code developed by Steve Scott from https://spreadsheetplanet.com
Sub OpenFileBasedOnName()
    Dim wkbk As Workbook
    Dim PathOfFolder As String
    Dim PathOfFile As String
        PathOfFolder = "D:\Local Disk D\Excel Examples\"
        PathOfFile = Dir(PathOfFolder & "*Budget*" & "*.xls*")
        Do Until PathOfFile = ""
            Set wkbk = Workbooks.Open(PathOfFolder & PathOfFile)
           PathOfFile = Dir
        Loop
End Sub

The word Budget is sandwiched between two asterisk wildcard characters in the file path.

This ensures that only workbooks with the word Budget in their file names are opened.

Example #5: How to Open a Workbook Encrypted with a Password Using VBA

A workbook encrypted with a password can only be opened when the password used to protect it is supplied.

Therefore, we must supply the correct password in the Excel VBA code. Otherwise, we get an error message.

Remember the Workbooks.Open method has fourteen optional arguments. One of the optional arguments is called Password:

password argument in workbooks.open method

Notice that the Password argument is number five in the list of arguments.

This means that once we have passed the required Filename argument to the procedure and we want to omit the intervening three optional arguments, we must place three commas before the Password argument.

We can see this in the example code below:

'Code developed by Steve Scott from https://spreadsheetplanet.com
Sub OpenExcelFileWithPassword()
Workbooks.Open "D:\Local Disk D\Excel Examples\Example 1.xlsx", , , Password:="bud*^&w"
End Sub

Example #6: Use Excel VBA Procedure to Launch the File Open dialog box

Sometimes our goal may be to use Excel VBA code to launch the Open dialog box so the user can navigate to the workbook they want to open.

The procedure for this purpose would look like the following:

'Code developed by Steve Scott from https://spreadsheetplanet.com
Sub LaunchOpenDiaologBox()
On Error Resume Next
    Dim PathOfFile As String
    PathOfFile = Application.GetOpenFilename()
    Workbooks.Open (PathOfFile)
End Sub

Note: We included the On Error Resume Next statement in the way of error handling to ensure that the Excel VBA does not return an error in case one clicks the Cancel button instead of selecting a workbook file.

When we run the code, the Open dialog box is launched, and one can select the file they want to open:

This tutorial has looked at five examples of how to open Excel files using VBA. We hope that you found the tutorial helpful.

Other Excel articles you may also like: