Excel VBA: Open a Workbook Using a Variable File Name

To open an Excel workbook whose name or path is stored in a VBA variable, pass that variable to Workbooks.Open: for example, Set wb = Workbooks.Open(Filename:=filePath). Declare the variable as a string, give it the full path to the file, and assign the returned workbook to a Workbook variable so your macro can work with the file afterward.

How Workbooks.Open uses a variable

Workbooks.Open is the Excel VBA method for opening an existing workbook. Its Filename argument accepts a string containing a workbook’s path and name. That string can be written directly in the call, but a variable is more useful when the filename changes, comes from a cell, or is assembled while the macro runs.

In the following example, the filename is combined with the folder containing the workbook that holds the macro. Save a copy of the workbook before running a macro; VBA actions cannot be undone with Ctrl+Z.

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

    ' Build a path beside the workbook containing this macro.
    filePath = ThisWorkbook.Path & Application.PathSeparator & "Monthly Report.xlsx"

    ' Open the target workbook and keep a reference to it.
    Set reportBook = Workbooks.Open(Filename:=filePath)
End Sub

The Set keyword is required when assigning an object such as a workbook. After the call, reportBook refers to the newly opened file. You can then use expressions such as reportBook.Worksheets(1).Name or pass the object to another procedure. Without a workbook variable, the file still opens, but it is harder to refer to that specific workbook reliably.

Build a complete path, not just a name

A filename such as Monthly Report.xlsx does not identify a unique file if it exists in several folders. Excel may look in its current or default folder, which can vary according to how the workbook was opened. For predictable results, provide an absolute path, such as a drive-and-folder path on Windows, or construct a path from a known folder.

Useful path sources include:

ThisWorkbook.Path is the folder containing the workbook that stores the VBA code. It is often appropriate when the source workbook and target file travel together.
Application.PathSeparator supplies the separator used by the current Excel environment. Using it between a folder and filename avoids hard-coding a separator.
A cell, named range, configuration sheet, or user selection can supply a path when different users or reporting periods use different files.

Do not confuse ThisWorkbook with ActiveWorkbook. ThisWorkbook means the file containing the running code; ActiveWorkbook means the workbook currently in front. Opening another file often changes the active workbook, so a path based on ActiveWorkbook.Path can point somewhere unexpected after the macro has opened a file.

If the macro workbook has never been saved, ThisWorkbook.Path is an empty string. Save it first or obtain the target folder another way. A path to a network share can work if the user has access and the location is available. A locally synced OneDrive or SharePoint folder can often be treated as a normal folder; a web URL is not always interchangeable with a local file path.

Get the filename from a variable or worksheet

The variable can hold the whole path or only the changing part. For example, a report year might come from a worksheet cell while the folder and filename pattern stay fixed. Make sure the cell contains text in the expected format and that the resulting path includes the file extension.

With the report year in cell B2 of the macro workbook’s first worksheet, build the path like this:

Sub OpenYearlyReport()
    Dim reportYear As String
    Dim filePath As String
    Dim reportBook As Workbook

    ' Read the year as text and construct the target filename.
    reportYear = CStr(ThisWorkbook.Worksheets(1).Range("B2").Value)
    filePath = ThisWorkbook.Path & Application.PathSeparator & _
               "Sales " & reportYear & ".xlsx"

    Set reportBook = Workbooks.Open(Filename:=filePath)
End Sub

If B2 contains 2026, the resulting filename is Sales 2026.xlsx in the macro workbook’s folder. The example assumes that exact file exists. If users can enter the year, consider checking that the cell is not blank and contains an allowed year before trying to open the file. A constructed name is only as reliable as its source values and naming convention.

Check that the file exists and handle errors

A missing file, misspelled extension, unavailable network drive, or permission problem can make the open operation fail. A simple existence check can give a clearer message before calling Workbooks.Open. The Dir$ function returns an empty string when it cannot find the specified file.

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

    ' Build the expected path beside the macro workbook.
    filePath = ThisWorkbook.Path & Application.PathSeparator & "Monthly Report.xlsx"

    If Len(Dir$(filePath)) = 0 Then
        MsgBox "The file was not found: " & filePath, vbExclamation
        Exit Sub
    End If

    ' Open the file only after the path check succeeds.
    Set reportBook = Workbooks.Open(Filename:=filePath)
End Sub

This check is useful for ordinary local or network file paths, but it is not a guarantee that the later open will succeed. The file could become unavailable between the check and the open, or Excel could reject it for another reason. For a production macro, add an error handler around the open operation and report the error in a way that helps the user identify the path or access issue. Avoid suppressing every error without explanation: that can make a failed open look like a successful run.

Choose what happens when the workbook opens

Workbooks.Open has optional arguments for common opening choices. For instance, ReadOnly:=True opens the file without intending to edit its saved contents. UpdateLinks:=0 tells Excel not to update external workbook links during opening; this may leave linked values unchanged from their saved state. Choose options based on what the macro needs rather than adding them automatically.

For a read-only report where external links should not update, the call can be written as Set reportBook = Workbooks.Open(Filename:=filePath, UpdateLinks:=0, ReadOnly:=True). The variable filePath must already contain the full path. If the macro needs current linked data or must save changes, these settings may be inappropriate. Opening a password-protected workbook also requires the relevant password argument or a user prompt; do not put passwords in a workbook or macro that other people can inspect.

Excel may display prompts for links, passwords, file formats, or other conditions. The exact prompt depends on the workbook and the arguments used. Do not disable alerts broadly just to make a macro run unattended unless you understand which prompts are being suppressed and have handled those cases deliberately.

Prevent opening the same workbook twice

Running a macro repeatedly can result in multiple open instances of the same file. Before opening, compare the target path with the FullName of workbooks already open in the Excel instance. If a match exists, reuse that workbook object instead of opening another copy.

For a path assembled into filePath, a loop over Application.Workbooks can look for a match using StrComp(candidate.FullName, filePath, vbTextCompare). Assign the matching candidate to your workbook variable and skip the open call. The text comparison ignores differences in letter case, which is generally useful for Windows file paths. Be mindful that aliases, mapped drives, and network paths can refer to the same file using different text; a simple string comparison may not recognize those as identical.

Also decide what the macro should do if the workbook is already open but has unsaved changes. Reusing it preserves the open session and its edits; closing it or opening another copy can surprise the user or risk losing work. Make that behavior explicit rather than assuming an already-open workbook is disposable.

Let the user choose a workbook

If the filename is not known in advance, Excel’s Application.GetOpenFilename method displays a file-selection dialog and returns the selected path. The method returns False if the user cancels, so check the result before passing it to Workbooks.Open.

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

    ' Ask the user to select an Excel file.
    selectedFile = Application.GetOpenFilename( _
        FileFilter:="Excel Files (*.xls*),*.xls*", _
        Title:="Select a workbook to open")

    If VarType(selectedFile) = vbBoolean Then Exit Sub

    selectedPath = CStr(selectedFile)
    Set chosenBook = Workbooks.Open(Filename:=selectedPath)
End Sub

The dialog is often safer than asking someone to type a long path, and it lets the user navigate to a different folder. It does not open the workbook by itself; the macro still calls Workbooks.Open. On Windows, Excel for Microsoft 365 supports this VBA approach. Excel for the web does not run VBA macros. Excel for Mac supports VBA, but the dialog and path conventions may differ from Windows, so test a cross-platform macro on the Mac versions your users actually use.

Common problems and safe habits

Wrong variable type: Store a path in a String. Store the opened workbook in a variable declared As Workbook, and use Set to assign it.
Relative path confusion: A filename without a folder can depend on Excel’s current folder. Build a known full path or ask the user to select the file.
Extension mismatch: The actual file may be .xlsm, .xlsb, or another supported format rather than .xlsx. Match the real filename, including its extension.
Wrong workbook reference: Prefer the workbook variable returned by Workbooks.Open over ActiveWorkbook when subsequent steps must target the file you just opened.
Untrusted content: Opening a workbook can expose users to external links or potentially unsafe content. Open files only from sources you trust and follow your organization’s security settings.

Macros can change or save data in the opened workbook, and their actions cannot be reversed with Ctrl+Z. Keep a backup before running code that edits or closes files. For Windows Excel, Alt+F11 opens the Visual Basic Editor; on Mac, the shortcut and keyboard mapping can vary, and the editor is also available from the Developer tab. Excel for the web has no VBA editor or macro execution.

Frequently Asked Questions

Can I pass only the workbook name to Workbooks.Open?
Yes, but Excel must be able to locate that name from its current or default folder. For dependable behavior, pass the full path or let the user select the file.

How do I open a workbook whose name is in a cell?
Read the cell into a string variable, combine it with the correct folder and extension, then pass the resulting path to Workbooks.Open(Filename:=filePath). Check for blank or unexpected cell values before opening.

How do I make the opened workbook read-only?
Use ReadOnly:=True in the Workbooks.Open call. This is appropriate when the macro only needs to inspect the workbook and should not save edits to it.

Why does my macro open the wrong workbook?
A relative filename may be resolving from an unexpected folder, or later code may use ActiveWorkbook after focus has changed. Build a full path and keep the workbook object returned by the open call.

Can a VBA macro open a workbook in Excel for the web?
No. Excel for the web can open and edit workbooks but does not run VBA. Run the macro in the desktop Excel application; Microsoft 365 on Windows is the context for the examples above.

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.