To open another Excel file with VBA, call Workbooks.Open and supply the file’s full path. For example, Set targetWb = Workbooks.Open(Filename:="C:\Reports\Quarterly.xlsx") opens that workbook in desktop Excel for Windows; the examples below show how to choose a file, control how it opens, and handle errors.
What the VBA command does
Workbooks.Open is an Excel VBA method that opens a workbook and returns a Workbook object. Assigning that object to a variable gives the macro a reliable reference to the file, even when more than one workbook is open. The workbook being opened becomes the active workbook in many ordinary cases, but using the object variable is safer than relying on which file happens to be active.
Here is a minimal macro. Change the path to the location and name of your file before running it:
Sub OpenAnotherWorkbook()
Dim filePath As String
Dim targetWb As Workbook
' Set this to the full path of the workbook to open.
filePath = "C:\Reports\Quarterly.xlsx"
Set targetWb = Workbooks.Open(Filename:=filePath)
End Sub
The macro opens the file in the current Excel application. It does not copy the file into the workbook that contains the code, and it does not automatically transfer data. The new file is a separate open workbook, which the macro can then read, edit, or close through targetWb.
Where to put and run the macro
In Excel for Microsoft 365 on Windows, open the workbook that will contain the macro, press Alt+F11 to open the Visual Basic Editor, then choose Insert > Module. Paste the code into the module and edit the path. Return to Excel and run the macro with Alt+F8: select OpenAnotherWorkbook and choose Run. If the Developer tab is available, Developer > Visual Basic opens the editor and Developer > Macros opens the macro list.
If the Developer tab is hidden in Windows Excel, use File > Options > Customize Ribbon and select Developer. Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm); saving as .xlsx removes VBA code. Save a backup copy before running a macro: VBA actions generally cannot be reversed with Ctrl+Z, so a mistaken edit or save may not be recoverable through Undo.
Excel for the web can open and edit many workbooks, but it cannot create, run, or edit VBA macros. Use the Excel desktop application to run this code. In Excel for Mac, VBA is available in the desktop app; the editor shortcut can depend on the keyboard and function-key settings, so use Tools > Macro > Visual Basic Editor if a shortcut does not work.
Use the correct file path
The path must identify a file Excel can reach, not just a folder. On Windows, a local path commonly looks like C:\Reports\Quarterly.xlsx. A network location may use a UNC path such as \\Server\Shared\Quarterly.xlsx, provided the user has access and the network is available. The extension should match the actual file, such as .xlsx, .xlsm, or .xls.
Spaces in a path are fine when the path is inside a VBA string, as in C:\Monthly Reports\June.xlsx. In VBA string literals, a backslash does not need to be doubled. A common mistake is to provide only the file name when it is not in Excel’s current folder; another is to use a path from a different computer or a disconnected drive.
Cloud-synced files need particular care. A OneDrive or SharePoint file may have a local synchronized path, or it may be available only through a web link. Workbooks.Open expects a usable file name or supported address; a copied sharing link is not necessarily the same as a local file path. For a macro intended for several people, a file picker or a configurable folder setting is often more dependable than embedding one person’s profile path.
Choose how the workbook opens
Workbooks.Open accepts named arguments that let you specify common opening behavior. For example, this version opens the file read-only and tells Excel not to update external links automatically:
Sub OpenReadOnlyWorkbook()
Dim filePath As String
Dim targetWb As Workbook
' Open without making changes to the source file.
filePath = "C:\Reports\Quarterly.xlsx"
Set targetWb = Workbooks.Open( _
Filename:=filePath, _
UpdateLinks:=0, _
ReadOnly:=True)
End Sub
ReadOnly:=True is useful when a macro only needs to inspect or copy data, or when another user may be editing the source. It does not make the file confidential or prevent someone from opening it separately. UpdateLinks:=0 avoids updating external workbook links during the open operation; it does not remove those links from the workbook. If the workbook depends on refreshed linked data, decide whether suppressing updates is appropriate.
Other situations may require a password or a decision about read-only recommendations. Do not put a real password in plain text in a macro: anyone who can inspect the VBA project may be able to see it, and password handling should follow the organization’s security rules. If the file prompts for credentials or has a format-specific restriction, the macro may need different arguments or user interaction rather than a hard-coded workaround.
Let the user pick the file
If the exact file changes each time, use Excel’s file picker instead of asking someone to edit a path in the code. Application.GetOpenFilename displays a selection dialog and returns either a selected file name or False if the user cancels.
Sub OpenWorkbookFromPicker()
Dim selectedFile As Variant
Dim targetWb As Workbook
' Ask the user to choose an Excel workbook.
selectedFile = Application.GetOpenFilename( _
FileFilter:="Excel Workbooks (*.xlsx;*.xlsm;*.xls),*.xlsx;*.xlsm;*.xls", _
Title:="Select a workbook")
If selectedFile = False Then Exit Sub
Set targetWb = Workbooks.Open(Filename:=CStr(selectedFile))
End Sub
The cancellation check matters: without it, the macro may pass a Boolean value where Excel expects a file name and produce an error. A picker also reduces path-typing mistakes, although the user still needs permission to access the chosen file. On Mac, using the picker is especially helpful when the file’s path format differs from the Windows examples.
Handle missing files and other errors
A path can be correct in the code but unavailable when the macro runs: the file may have been renamed, moved, or stored on an offline network drive. Opening can also fail because of permissions, a damaged file, a password prompt, or a file already being handled in a way that conflicts with the requested access. Add an error handler so the user sees a useful message rather than an unexplained VBA interruption.
Sub OpenWorkbookWithErrorHandler()
Dim filePath As String
Dim targetWb As Workbook
' Use a path that exists and is accessible to this user.
filePath = "C:\Reports\Quarterly.xlsx"
On Error GoTo OpenFailed
Set targetWb = Workbooks.Open( _
Filename:=filePath, _
ReadOnly:=True, _
UpdateLinks:=0)
MsgBox "Opened " & targetWb.Name, vbInformation
Exit Sub
OpenFailed:
MsgBox "Excel could not open the workbook." & vbCrLf & _
"Details: " & Err.Description, vbExclamation
End Sub
The handler reports Excel’s error description, which can help distinguish a missing file from another problem. For a larger macro, keep the error-handling scope narrow and add a separate cleanup section if the procedure creates other objects or changes application settings. Avoid hiding every error with On Error Resume Next: that can let the macro continue with an empty or invalid workbook reference and make the eventual failure harder to diagnose.
Work with the opened workbook
Once targetWb refers to the opened file, qualify worksheet and range references with that workbook. For example, targetWb.Worksheets("Summary").Range("A1").Value explicitly refers to cell A1 on the Summary sheet in the file you opened. This is more reliable than ActiveWorkbook or an unqualified Range("A1"), because the active workbook or sheet can change while a macro runs.
Check that the expected sheet exists before using it if the file is supplied by another person or may change over time. A misspelled sheet name causes a runtime error. Also consider whether the source workbook is read-only: reading values is fine, but saving changes back to a read-only workbook is not the intended workflow. If the macro needs to preserve the source, copy needed values into the macro workbook or another destination rather than editing the original.
Close the file without losing work
Opening a workbook leaves it open until the user closes it or VBA closes it. To close a workbook that the macro opened, use its object variable so that the intended file is addressed. For a source opened only for reading, targetWb.Close SaveChanges:=False closes it without saving changes. Use that only when discarding changes is intended; it is not a safe default if the macro has edited data that must be kept.
If the procedure should leave the workbook open for review, do not close it. If it should save edits, save deliberately before closing and account for read-only status, file permissions, and whether the original should be overwritten. A practical pattern is to track the workbook returned by Workbooks.Open and close that same object, rather than closing whichever workbook happens to be active at the end of the macro.
Security and platform considerations
Only open files from sources you trust. A workbook can contain macros, external links, or other active content, and opening it is not the same as confirming that its contents are safe. Excel’s Trust Center and organizational policies govern macro behavior; do not lower security settings just to make an unfamiliar file run. Read-only mode limits accidental editing but is not a security boundary.
The VBA examples here target desktop Excel for Microsoft 365 on Windows. Excel for Mac supports VBA, but a hard-coded Windows drive path such as C:\Reports\Quarterly.xlsx will not point to a Mac file. Use a Mac-appropriate path or, more portably for a person choosing a file, the file picker. VBA does not run in Excel for the web. If the workbook must be processed in a browser, use a non-VBA option that is available in that environment.
When to use a different approach
VBA is suitable when a desktop Excel macro needs to open a workbook as part of a repeatable process. If the task is simply to let a person open a file, use File > Open rather than writing a macro. If the goal is to combine or refresh data from files on a regular basis, Power Query may be easier to maintain: in Windows Excel, use Data > Get Data to connect to available file sources and configure a refresh. The right choice depends on whether you need an interactive open, automated workbook actions, or a repeatable data-import process.
Frequently Asked Questions
How do I open another Excel file with a button? Add a button through Developer > Insert and assign it to a public macro such as OpenAnotherWorkbook. The macro must be stored in a workbook that allows macros, and the button will run the code with the path or file-picker behavior you programmed.
Can VBA open a workbook without making it active? Workbooks.Open opens the workbook in the Excel application, and Excel may activate it. Store the returned Workbook object in a variable and refer to that object directly; do not rely on the active workbook to identify your target.
How do I open a workbook read-only? Include ReadOnly:=True in the Workbooks.Open call. This asks Excel to open the file without editing the source through that session, which is useful for reading, but it is not a substitute for access controls or a backup.
Why does my VBA say the file cannot be found? Check the complete path, spelling, extension, drive availability, and the account’s access to the location. A path copied from another computer may not exist on yours. If users select different files, replace the hard-coded location with Application.GetOpenFilename.
Can I run this VBA in Excel for the web? No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel to use the macro, or choose a web-compatible tool for the task.
Related
- Excel Meal Prep Template: Plan Meals, Shop Smarter
- Excel Template for Quotation Comparison: Build One
- Excel Task List Template: Set Up and Customize Yours
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Gamepad Macro Recorder: How It Works and What to KnowA gamepad macro recorder captures a sequence of controller inputs—such as button presses, stick movements, and pauses—and plays…
- Excel VBA Date Today: How to Insert the Current DateIn Excel VBA, use the built-in Date function to get today’s date. To put it in a worksheet…
- MS Access Split String: VBA Syntax and ExamplesYes. In Microsoft Access, VBA’s Split function breaks a string into a zero-based array of pieces using a…
- Excel VBA Freeze Worksheet: Freeze Panes or Lock CellsIn Excel VBA, use ActiveWindow.FreezePanes to keep rows or columns visible while you scroll; use worksheet protection when…
- Access VBA Find in String: InStr, Options, and ExamplesIn Access VBA, use InStr to find one string inside another: it returns the 1-based position of the…
- VBA MsgBox: How to Display a VariableYes. To display a VBA variable in a message box, join it to a text string with the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.