Open Workbook VBA: Excel Methods and Examples

To open an Excel workbook with VBA, call Workbooks.Open and provide its full file path. For example, assign the result to a Workbook variable so your macro can refer to the file directly; choose options such as read-only access when the task calls for them.

What “open workbook VBA” means

In Excel VBA, a workbook is an Excel file that has been opened in the current Excel application. The Workbooks collection represents the open workbooks, and its Open method loads another workbook. A macro can then read cells, update data, save changes, or close the file. This is different from opening a worksheet: a workbook can contain several worksheets, charts, and other Excel content.

The method is useful for repeatable tasks, such as importing a daily report, checking a list of files, or updating a central summary from a source workbook. It can open a workbook in the same Excel instance as the macro, so the code can work with both files without asking the user to switch between windows.

One important distinction is which workbook the code refers to. ThisWorkbook means the workbook that contains the running VBA code. ActiveWorkbook means whichever workbook is currently active in Excel, which may change after another file opens. When a macro opens a file, keeping the returned workbook in a variable is usually clearer and safer than relying on ActiveWorkbook.

Open a workbook with Workbooks.Open

For a known file location, use a full path and set a workbook variable to the method’s return value. Before running a macro that changes files, save a separate copy of important workbooks: macro actions cannot be undone with Ctrl+Z.

Sub OpenReport()
    Dim reportBook As Workbook
    Dim filePath As String

    ' Set this to the report's full path.
    filePath = "C:\Reports\April.xlsx"

    ' Open the workbook without updating external links.
    Set reportBook = Workbooks.Open(Filename:=filePath, UpdateLinks:=0)
End Sub

Change the example path to a real location on your computer. On Windows, a path commonly looks like C:\Reports\April.xlsx; a network location might look like \\Server\Shared\April.xlsx. The file name and extension must match the actual file. If the workbook is in the same folder as the macro workbook, you can build a path from ThisWorkbook.Path, but check that the macro workbook has been saved first because an unsaved workbook has no folder path.

Workbooks.Open returns a reference to the workbook that it opened. The variable in the example lets later code refer to it explicitly, for instance as reportBook.Worksheets("Sheet1").Range("A1"). Replace the sheet name with one that exists in the source file; otherwise, code that tries to use a nonexistent worksheet will raise an error.

Where to put and run the macro

In Excel for Microsoft 365 on Windows, first save a macro-containing workbook as an Excel Macro-Enabled Workbook (.xlsm). If the Developer tab is hidden, show it through File > Options > Customize Ribbon, then select Developer. Choose Developer > Visual Basic to open the VBA editor. In the editor, use Insert > Module, paste the procedure into the module, and change the file path. Return to Excel and run it from Developer > Macros, or press Alt+F8, select the macro, and choose Run.

Alt+F11 opens or switches to the Visual Basic Editor on many Windows keyboards. On a Mac, the editor shortcut is commonly Option+F11, sometimes with the Fn key depending on keyboard settings. Excel for Mac has a Visual Basic Editor and supports many VBA tasks, but Windows-specific paths, dialogs, or automation may need changes. Excel for the web cannot create, run, or edit VBA macros; open the file in the desktop Excel app to use VBA.

Excel may display a security warning when a workbook containing macros is opened. Only enable macros in files you trust. A macro can modify or delete files, so do not treat a workbook as safe merely because it arrived as an Excel attachment.

Choose options for links, read-only access, and passwords

The Open method accepts optional named arguments, so you can state the settings that matter without filling in every possible argument. For instance, UpdateLinks:=0 prevents Excel from updating external references when it opens the file. That can be appropriate when the macro only needs to inspect saved values; if current linked data is required, decide how those links should be updated rather than silently assuming they are current.

Use ReadOnly:=True when the macro should inspect a source file without changing it. Read-only mode is a safeguard against accidental edits, not a substitute for a backup. If you later need to save changes, the workbook may have to be reopened with write access or saved under another name. A file already open by another user or process can also affect whether it opens for editing.

Sub OpenSourceReadOnly()
    Dim sourceBook As Workbook
    Dim filePath As String

    ' Set this to the source workbook's full path.
    filePath = "C:\Data\Source.xlsx"

    ' Open for inspection and do not update external links.
    Set sourceBook = Workbooks.Open( _
        Filename:=filePath, _
        UpdateLinks:=0, _
        ReadOnly:=True)
End Sub

If a file requires a password to open, VBA has password-related arguments, but putting a password directly in a macro stores it as readable text in the code. That is not a secure way to protect a password. Avoid embedding credentials in shared workbooks, and follow your organization’s approved process for protected files. A password required to modify a workbook is distinct from one required to open it.

Let the user choose a workbook

A hard-coded path is convenient for a fixed process but breaks if someone moves or renames the file. For an interactive macro, Application.GetOpenFilename displays Excel’s file selection dialog and returns the selected path. If the user cancels, it returns Boolean False, so test for cancellation before passing the result to Workbooks.Open.

Sub ChooseAndOpenWorkbook()
    Dim selectedFile As Variant
    Dim chosenBook As Workbook

    ' Ask the user to select an Excel workbook.
    selectedFile = Application.GetOpenFilename( _
        FileFilter:="Excel workbooks (*.xlsx;*.xlsm;*.xls),*.xlsx;*.xlsm;*.xls", _
        Title:="Select a workbook")

    ' Stop cleanly if the user cancels.
    If VarType(selectedFile) = vbBoolean Then Exit Sub

    ' Open the selected workbook.
    Set chosenBook = Workbooks.Open(Filename:=CStr(selectedFile))
End Sub

The filter in this example includes the common .xlsx, .xlsm, and older .xls formats. Add other extensions only if the macro is designed to handle them. The dialog lets the user browse, but it does not validate that the chosen workbook has the sheet names or layout your later code expects. Include checks for those assumptions before changing data.

Work with the opened file and close it carefully

After opening a workbook, use the variable you assigned rather than relying on whichever window is active. For example, qualify worksheet and range references with that variable. This reduces the chance that a command intended for the source file accidentally affects the macro workbook.

When the task is finished, decide whether the macro should save changes. If it only reads data, close without saving; if it made intended edits, save deliberately. Closing a workbook with unsaved changes can prompt Excel or discard work, depending on the arguments and application state. For a routine that must never save the source, closing with SaveChanges:=False makes that choice explicit. Do not use it if the macro has made changes that should be retained.

It can also be useful to check whether a workbook is already open before opening it again. Duplicate instances can lead to confusing prompts or edits in the wrong copy. A reliable check compares each open workbook’s full name with the target path, accounting for the fact that paths can differ in capitalization or formatting. For a small one-off macro, simpler code may be enough; for a shared tool, consider handling an already-open workbook and informing the user which copy will be used.

Common errors and how to diagnose them

“File not found” or error 1004: Check the full path, spelling, extension, network access, and whether the file has been moved. A mapped network drive may not be available in every user’s session; try an approved shared path.
The workbook opens but later code fails: Confirm that the expected worksheet name and cell layout exist. Opening a file successfully does not prove that it contains the data structure the macro expects.
A password prompt appears: The file may be encrypted, protected for editing, or linked to a protected source. Do not assume that ReadOnly:=True bypasses an opening password.
External-link prompts or unexpected values: Decide whether links should update. UpdateLinks:=0 suppresses link updates for the open operation; it does not repair broken links or make stored values current.
The macro is blocked: Review Excel’s macro security settings and the file’s source. In Windows Excel, the settings are under File > Options > Trust Center > Trust Center Settings > Macro Settings. Do not lower security broadly just to run an unknown file; use a trusted, approved location or ask the administrator.

For a production macro, add error handling so the user receives a useful explanation and Excel does not remain in a confusing state. Handle predictable issues such as a missing file, a canceled selection, or a workbook already open. Avoid catching every error and then continuing as if the workbook had opened: subsequent code could read the wrong workbook or produce misleading results.

Safety, file formats, and alternatives

Opening a workbook through VBA does not automatically run its macros simply because it is opened by another macro. Excel’s security policy governs whether macros in the opened file are allowed to run. Still, opening an untrusted workbook can expose the user to risky content, external connections, or misleading data. Use trusted files and review link and macro prompts rather than suppressing every warning globally.

Save the code in .xlsm or another macro-capable format if you need it to remain in the workbook. Saving a macro workbook as .xlsx removes VBA code. Keep an untouched copy of source data when the process is important, and test with sample files before running a macro against a large collection or shared production workbook. Remember that Ctrl+Z does not undo VBA changes.

If the task is occasional, opening the file through File > Open may be simpler than writing a macro. For repeatable data imports, Power Query can be a better fit because it is designed to connect to and transform data with refreshable steps. Excel for the web also offers Office Scripts for some automation scenarios, but Office Scripts are TypeScript-based and are not VBA. Choose VBA when the process specifically needs desktop Excel automation or existing VBA integration.

Frequently Asked Questions

What is the VBA command to open an Excel file?
Use Workbooks.Open, supplying the file path with the Filename argument. Assign the returned workbook to a variable when you need to refer to it later.

How do I open a workbook without updating links?
Pass UpdateLinks:=0 to Workbooks.Open. This prevents external links from being updated during that open operation; it does not refresh or fix those links.

Can VBA open a workbook as read-only?
Yes. Use ReadOnly:=True in the call to Workbooks.Open. Read-only mode helps avoid accidental edits, but it does not replace keeping a backup copy.

How can a macro ask me which workbook to open?
Use Application.GetOpenFilename to display a file picker, check whether the result is False because the user canceled, and then pass the selected path to Workbooks.Open.

Can I run VBA to open a workbook in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac, bearing in mind that some operating-system-specific VBA may need adjustment.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.