One of the most common uses of VBA is to programmatically open and interact with Excel workbooks.
To open a workbook from your computer or a network location using VBA, use the ‘Open’ method of the ‘Workbooks’ object (the Workbooks.Open method).
In this tutorial, I will show you ten ways to open an Excel workbook using VBA. But first, let’s look at the syntax of the ‘Open’ method.
Workbooks.Open Method in VBA
The syntax of the ‘Open’ method is as follows:
expression.Open (FileName, UpdateLinks, ReadOnly, Format, Password, WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter, Editable, Notify, Converter, AddToMru, Local, CorruptLoad)
Note: expression is a variable representing a ‘Workbooks’ object.
The following is an explanation of the method’s arguments:
- Filename (Required): This is the full path and filename of the workbook you want to open. You should provide the complete file path as a string, including the file extension (e.g., “C:\Excel Tutorials\Student List.xlsx”).
- UpdateLinks (Optional): This argument specifies whether to update external links in the workbook. Set to 0 to not update links or 3 to update them. If this argument is omitted, the user will be prompted to specify how to update the links. An example of an external link is the reference to a range in the “Sales.xlsx” workbook in the formula =SUM([Sales.xlsx]2023!B2:B10).
- ReadOnly (Optional): This boolean value specifies whether to open the workbook in read-only mode. Set to ‘True’ to open the workbook as read-only or ‘False’ for editing. By default, it is ‘False.’
- Format (Optional): This integer specifies the file format in which the workbook should be opened. When opening a text file with Microsoft Excel, you can use this argument to specify the delimiter character. The available options are 1 for tabs, 2 for commas, 3 for spaces, 4 for semicolons, and 5 for nothing. Additionally, you can specify a custom character by using option 6. If you omit this argument, Excel will use the current delimiter.
- Password (Optional): If the workbook is password-protected and requires a password, it can be provided as a string to open it. If not provided and a password is needed, you will be prompted for the password.
- WriteResPassword (Optional): This argument contains the write password for a write-reserved workbook. If omitted and the workbook has a password, the user will be prompted.
- IgnoreReadOnlyRecommended (Optional): This is a boolean value that determines whether to display a message recommending the file to be read-only if saved with the Read-Only Recommended option. If you want to display the message, set it to ‘True,’ otherwise set it to ‘False.’ The default value is ‘False.’
- Origin (Optional): This argument specifies the system that created the file. This argument is useful when working with files created in other applications. When working with text files, this argument specifies the original file source, essential for accurately mapping code pages and Carriage Return/Line Feed (CR/LF). You can use one of the following XlPlatform constants to specify the source: xlMacintosh, xlWindows, or xlMSDOS. If you don’t specify this argument, the current operating system will be used by default.
- Delimiter (Optional): When the file is a text file, and the Format argument is set to 6, the Delimiter argument specifies the character that will be used to separate the values. The Delimiter argument should be a string, and only the string’s first character will be used. You can use the following characters as delimiters: Chr(9) for tabs, “,” for commas, “;” for semicolons, or any custom character you prefer.
- Editable (Optional): This is a boolean value determining whether the file can be edited after opening it. If the file is an Excel template, set the value to ‘True’ to allow editing of the specified template. Set it to ‘False’ to open a new workbook based on the specified template without allowing any changes to the original template. The default value for the argument is ‘False.’
- Notify (Optional): This is a boolean value that determines whether or not the user should receive a notification when opening a workbook. When set to ‘True,’ Excel will open the file as read-only if it cannot be opened in read/write mode. It will then poll the notification list and notify the user when the file becomes available. If this argument is set to ‘False’ or omitted, no notification will be sent, and any attempts to open an unavailable file will fail.
- Converter (Optional): This integer specifies the file converter to use when opening the file.
- AddToMru (Optional): This is a boolean value that specifies whether to add the file to the Most Recently Used (MRU) list in Excel.
- Local (Optional): This is a boolean value that specifies whether to open the file with the regional settings from the current user’s system. Set to ‘True’ to use the local settings or ‘False’ to use the settings from the file.
- CorruptLoad (Optional): Three constants are available: ‘xlNormalLoad,’ ‘xlRepairFile,’ and ‘xlExtractData.’ If no value is specified, the default behavior is ‘xlNormalLoad,’ which does not attempt recovery.
While I’ve explained all the arguments that the Workbooks.Open method can take, in practice, you won’t need to use more than a couple in most cases.
Also read: Create New Workbook Using VBA in Excel
Scenario #1: Open a New Workbook and Save it
To open a new workbook and save it, you can use the following code snippet, which you can modify to suit your needs:
Sub Example_1()
' Declare a Workbook variable to hold the new workbook
Dim NewWorkbook As Workbook
' Declare a String variable to hold the file path
Dim FilePath As String
' Create a new workbook and set it to the NewWorkbook variable
Set NewWorkbook = Workbooks.Add
' Specify the file path where the new workbook will be saved
FilePath = "C:\Regional Sales\Texas Sales.xlsx"
' Save the new workbook using the specified file path
NewWorkbook.SaveAs FilePath
' Close the new workbook
NewWorkbook.Close
End Sub
This code is designed to create a new workbook, save it in a specific directory, and then close it.
It first declares two variables: NewWorkbook to hold the new workbook object and FilePath to store the path where the new workbook will be saved.
The Workbooks.Add method is then used to create a new workbook, which is then stored in NewWorkbook. The SaveAs method is employed to save the new workbook at the location specified in FilePath.
Finally, the workbook is closed using the Close method. Make sure the specified directory exists before running the code to avoid any issues.
Scenario #2: Open a Workbook From a File Path
To open a workbook from a file path, you can use the following code snippet, which you can modify to suit your needs.
Sub Example_2()
' Declare a String variable to store the file path
Dim FilePath As String
' Specify the file path for the workbook to open
FilePath = "C:\Regional Sales\Texas Sales.xlsx"
' Check if the workbook exists at the specified file path
If Dir(FilePath) = "" Then
' Display a message box if the workbook is not found
MsgBox "Workbook not found"
Else
' Open the workbook if it exists
Workbooks.Open FilePath
End If
End Sub
This code aims to open a workbook located in a specific directory. It first declares a variable called FilePath to store the location of the workbook. The Dir function is then used to check if a workbook exists at that location.
If the workbook is not found, a message box pops up to notify you and show a message “Workbook not found”—otherwise, the Workbooks.Open method is used to open the workbook.
Also read: Run Macro In Another Workbook Using VBA
Scenario #3: Open Workbooks From a List of File Paths
You may want to open multiple workbooks based on a list of file paths in an Excel worksheet.
The following code snippet opens workbooks based on a list of paths in a cell range:
Sub Example_3()
' Declare a Range variable for the list of file paths
Dim PathListRange As Range
' Declare a Range variable for each individual cell in the list
Dim Cell As Range
' Declare a Workbook variable
Dim wb As Workbook
' Set the range of cells that contain the file paths in Sheet1
Set PathListRange = ThisWorkbook.Sheets("Sheet1").Range("A1:A10")
' Loop through each cell in the specified range
For Each Cell In PathListRange
' Check if the cell contains a file path
If Cell.Value <> "" Then
' Open the workbook corresponding to the file path
Workbooks.Open (Cell.Value)
End If
Next Cell
' Display a message box when all workbooks have been opened
MsgBox "All workbooks in the list have been opened."
End Sub
The purpose of this VBA code is to open multiple workbooks whose file paths are listed in a specific range (A1:A10) of a worksheet (Sheet1).
The code opens multiple Excel workbooks specified by a list of file paths stored in a specific range on Sheet1.
The code iterates through each cell in the range, opens the corresponding workbook, and sends a notification to the user when all workbooks have been successfully opened.
Also read: VBA Check If Workbook Is Open
Scenario #4: Open a Workbook and Activate a Specific Worksheet
To open a workbook and activate a specific worksheet, you can use the following code snippet, which you can modify to suit your needs:
Sub Example_4()
' Declare a Workbook variable to hold the opened workbook
Dim wb As Workbook
' Declare a String variable to hold the file path
Dim FilePath As String
' Specify the file path for the workbook to open
FilePath = "C:\Regional Sales\Texas Sales.xlsx"
' Check if the workbook exists at the specified file path
If Dir(FilePath) = "" Then
' Display a message box if the workbook is not found
MsgBox "Workbook not found."
Else
' Open the workbook and set it to the Workbook variable
Set wb = Workbooks.Open(FilePath)
' Activate the worksheet named "Sheet2" in the opened workbook
wb.Worksheets("Sheet2").Activate
End If
End Sub
The code first verifies if the specified workbook exists at the provided file path. If the workbook is found, the code opens it and activates Sheet2 within that workbook.
However, if the workbook is not found, the code displays a message indicating it was not found.
Also read: VBA Delete Files in Folder
Scenario #5: Open a Password Protected Workbook
To open a password-protected workbook, you can use the following code snippet, which you can modify to suit your needs:
Sub Example_5()
' Declare a String variable to store the file path
Dim FilePath As String
' Declare a String variable to store the password
Dim Password As String
' Specify the file path for the workbook to open
FilePath = "C:\Regional Sales\Illinois Sales.xlsx"
' Specify the password to open the workbook
Password = "##21"
' Check if the workbook exists at the specified file path
If Dir(FilePath) = "" Then
' Display a message box if the workbook is not found
MsgBox "Workbook not found."
Else
' Open the workbook using the file path and password
Workbooks.Open FilePath, Password:=Password
End If
End Sub
The code checks whether the specified workbook exists at the given file path and, if found, opens the workbook with the provided password.
If the file is not found, it displays a message indicating that the workbook was not found.
Note: You must supply the correct password. Otherwise, VBA will throw a runtime error 1004, indicating that the password you provided is not correct:
Also read: VBA to Create Multiple Files in a Folder
Scenario #6: Open an Excel Workbook as Read Only
When you open a workbook in read-only mode, you can access and review the data and content without making any changes, ensuring that the original data is not accidentally altered.
To open an Excel workbook in the read-only format, you can use the following code snippet, which you can adapt to suit your needs:
Sub Example_6()
' Declare a Workbook variable to hold the opened workbook
Dim wb As Workbook
' Declare a String variable to hold the file path
Dim FilePath As String
' Specify the file path for the workbook to open
FilePath = "C:\Regional Sales\Texas Sales.xlsx"
' Check if the workbook exists at the specified file path
If Dir(FilePath) = "" Then
' Display a message box if the workbook is not found
MsgBox "Workbook not found."
Else
' Open the workbook in read-only mode and set it to the Workbook variable
Set wb = Workbooks.Open(Filename:=FilePath, ReadOnly:=True)
End If
End Sub
The code checks if the specified Excel workbook exists at the designated file path.
If the workbook exists, it opens it as read-only, and if the file is not found, it displays a message indicating that the workbook was not found.
Also read: How to Make Excel File Read Only
Scenario #7: Open Workbook in the Background
When you open a workbook in the background, it is loaded and running but not visible on the screen.
To open an Excel workbook in the background, you can use the following code snippet, which can be adapted to suit your needs
Sub Example_7()
' Declare a new Excel Application object
Dim App As New Excel.Application
' Declare a String variable for the file path
Dim FilePath As String
' Declare a Workbook variable to hold the workbook
Dim Book As Excel.Workbook
' Make the new Excel Application invisible
App.Visible = False
' Specify the file path for the workbook to open
FilePath = "C:\Regional Sales\Texas Sales.xlsx"
' Check if the workbook exists at the specified file path
If Dir(FilePath) = "" Then
' Display a message box if the workbook is not found
MsgBox "Workbook not found."
Else
' Open the workbook and set it to the Workbook variable
Set Book = App.Workbooks.Open(FilePath)
' Activate "Sheet1" and modify cell A1 value to 2500
Book.Worksheets("Sheet1").Activate
Book.Worksheets("Sheet1").Range("A1") = 2500
' Close the workbook and save the changes
Book.Close SaveChanges:=True
' Quit the new Excel Application
App.Quit
End If
' Release the Excel Application object
Set App = Nothing
End Sub
The code initializes a new Excel Application object (App) and sets its visibility to False. This allows the code to operate in the background.
It then specifies the file path and checks if the workbook exists.
If it does, the workbook is opened, and its “Sheet1” is activated. Cell “A1” in this sheet is then updated with the value 2500. Finally, the workbook is saved and closed, and the application quits.
Make sure the specified directory and worksheet name are accurate to prevent errors.
Also read: VBA to Unprotect a Workbook
Scenario #8: Open Workbook Using Workbook Open File Dialog Box
You can use the ‘Application.GetOpenFilename’ method to display the ‘Open’ dialog box that allows the user to select a workbook to open.
The following is an example code that uses this method:
Sub Example_8()
' Declare a String variable to hold the path of the file to open
Dim FileToOpen As String
' Show a file dialog box to the user and store the selected file path
FileToOpen = Application.GetOpenFilename("Excel Files (*.xls; *.xlsx), *.xls; *.xlsx")
' Check if the user cancelled the file dialog box
If FileToOpen = "False" Then
' Display a message box and exit the macro if no file was selected
MsgBox "No file selected. Exiting the macro."
Exit Sub
End If
' Open the selected workbook
Workbooks.Open FileToOpen
End Sub
The code displays a file dialog for the user to choose an Excel workbook.
If the user selects a file, the workbook is opened. However, if the user cancels the file selection, the macro will exit without opening any files.
Also read: Open Folder and Select File Using VBA
Scenario #9: Open All Workbooks in a Specific Folder
To open all workbooks in a specific folder, you can use a loop to iterate through the files and open each workbook.
The following is a code snippet that opens all workbooks in a specific folder:
Sub Example_9()
' Declare String variables to hold the folder path and file name
Dim FolderPath As String
Dim FileName As String
' Declare Workbook and Worksheet variables
Dim wb As Workbook
Dim ws As Worksheet
' Specify the folder path where the workbooks are located
FolderPath = "C:\Regional Sales\"
' Check if the folder exists
If Dir(FolderPath, vbDirectory) = "" Then
' Display a message box and exit the macro if folder is not found
MsgBox "Folder not found. Exiting the macro."
Exit Sub
End If
' Initialize the file name with the first Excel file in the folder
FileName = Dir(FolderPath & "*.xls*")
' Loop through each Excel file in the folder
Do While FileName <> ""
' Open the workbook and set it to the Workbook variable
Set wb = Workbooks.Open(FolderPath & FileName)
' Update FileName to the next file
FileName = Dir
Loop
' Show a message box indicating all workbooks have been opened
MsgBox "All workbooks in the folder have been opened."
End Sub
The code checks whether the designated folder exists or not.
If the folder exists, it proceeds to open each Excel file and displays a message confirming that all the workbooks in the folder are now open.
However, if the folder does not exist, the program shows a message stating that the folder cannot be located and exits the macro.
Scenario #10: Open Workbook from Sharepoint
Workbooks can be stored in a central SharePoint location for multi-user access.
To open a workbook from SharePoint using VBA, you must provide the SharePoint URL as the file path.
The following is a sample VBA code that you can modify to suit your needs:
Sub Example_10()
' Declare String variables to hold the SharePoint URL, file name, and full path
Dim SharePointURL As String
Dim FileName As String
Dim FullPath As String
' Specify the base URL of the SharePoint site
SharePointURL = "https://example-sharepoint-site.com/"
' Specify the relative path of the Excel workbook within the SharePoint site
FileName = "Documents/Regional Sales/Texas Sales.xlsx"
' Concatenate SharePoint URL and file name to create the full path
FullPath = SharePointURL & FileName
' Open the workbook using the full path
Workbooks.Open FullPath
End Sub
The code first sets up the SharePoint URL and the relative file path for the workbook you want to open.
It then concatenates these two to create the full path to the file. Finally, the Workbooks.Open method opens the workbook located at the specified SharePoint URL.
This is particularly useful for businesses or teams that collaborate using SharePoint, as it allows you to programmatically access and manipulate files stored on SharePoint right from Excel.
Note: You must modify the code to handle authentication and permissions based on your Sharepoint setup.
Other Excel VBA articles you may also like:
