Excel VBA Open Workbook Dialog: A Practical How-To

To display a workbook picker in Excel VBA, call Application.GetOpenFilename, check whether the user cancelled, and pass the selected path to Workbooks.Open. The dialog only returns a file name; it does not open the workbook by itself.

What the workbook dialog does

An open-file dialog lets someone choose a workbook through Excel’s file picker instead of typing a path into a macro or embedding a fixed location in code. This is useful when files vary by user, folder, reporting period, or computer. The dialog can also limit the visible choices to particular file types, which helps people select the intended source file.

In VBA, the selection and the opening are separate actions. Application.GetOpenFilename displays the picker and returns the selected path as a value. If the user presses Cancel, it returns the Boolean value False. Workbooks.Open is the method that actually opens the workbook. Keeping these steps distinct makes it possible to handle cancellation before trying to open a file.

The simplest VBA example

Place this macro in a standard module in the workbook that will run it. In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, and paste in the code. Save a copy of the workbook before running any macro: VBA changes cannot be undone with Ctrl+Z.

Sub OpenWorkbookFromDialog()
    Dim selectedFile As Variant
    Dim openedWorkbook As Workbook

    ' Ask the user to choose an Excel workbook.
    selectedFile = Application.GetOpenFilename( _
        FileFilter:="Excel workbooks (*.xls*),*.xls*", _
        Title:="Select a workbook to open", _
        MultiSelect:=False)

    ' A Boolean result means the user cancelled.
    If VarType(selectedFile) = vbBoolean Then Exit Sub

    ' Open the chosen file and keep a reference to it.
    Set openedWorkbook = Workbooks.Open(Filename:=CStr(selectedFile))

    MsgBox "Opened: " & openedWorkbook.Name, vbInformation
End Sub

The filter *.xls* matches common Excel workbook extensions that begin with “xls,” including .xls, .xlsx, .xlsm, and .xlsb. The result is stored in a Variant because it may be either a path or the Boolean cancellation value. The VarType check exits before the macro tries to use a cancelled selection as a file name.

After the dialog closes with a selection, the macro opens that file and stores the resulting workbook object in openedWorkbook. That reference is useful if later code needs to read a worksheet or close the file. For example, a later statement could refer to openedWorkbook.Worksheets(1). The example opens the selected file normally, so Excel may allow edits according to the file’s permissions and settings.

Using FileDialog for more control

Application.FileDialog is another built-in option. It offers a file-picker dialog with configurable filters and an explicit selection collection. Use it when you want to offer several clearly named file types or prefer to work with the dialog’s SelectedItems collection. In Excel VBA, msoFileDialogFilePicker identifies the picker dialog.

Sub OpenWorkbookWithFileDialog()
    Dim openedWorkbook As Workbook

    ' Configure a picker for common Excel workbook formats.
    With Application.FileDialog(msoFileDialogFilePicker)
        .Title = "Choose an Excel workbook"
        .AllowMultiSelect = False
        .Filters.Clear
        .Filters.Add "Excel workbooks", "*.xls; *.xlsx; *.xlsm; *.xlsb"

        ' Show returns -1 when the user chooses a file.
        If .Show = -1 Then
            Set openedWorkbook = Workbooks.Open( _
                Filename:=.SelectedItems(1))
        End If
    End With
End Sub

The .Show result is -1 when the user confirms a selection; otherwise, the code simply reaches the end without opening anything. Since .AllowMultiSelect is set to False, the macro expects one path at .SelectedItems(1). If you change that setting to allow multiple selections, you need to loop through the selected items and decide how each workbook should be handled.

For a basic one-file prompt, GetOpenFilename is usually shorter. For a more configurable picker, FileDialog can be easier to extend. Neither method replaces Workbooks.Open: selecting a file and opening it remain separate steps in either approach.

Choosing file filters and handling the result

A filter guides the user but is not a complete validation or security check. With GetOpenFilename, FileFilter uses a description followed by a file pattern, separated by a comma. For example, FileFilter:="Excel workbooks (*.xls*),*.xls*" displays a description and shows matching Excel files. You can offer more than one filter by adding additional description-and-pattern pairs, separated by commas.

Choose patterns that match the files your macro can actually use. A macro that expects formulas and worksheets may not handle a CSV file as intended, even if you add CSV to the picker. Likewise, selecting an .xlsm file does not mean its macros should be trusted. The dialog narrows the visible choices; your code should still be prepared for missing sheets, unexpected layouts, read-only files, or other conditions that can arise after opening.

Use a specific title, such as “Choose the monthly source workbook,” so the user understands what the macro needs.
Use MultiSelect:=False when the rest of the macro is designed for one workbook.
Store the returned value in a Variant, not a String, so it can represent the cancellation result as well as a path.
Keep the returned workbook object in a variable if subsequent steps need to work with that workbook.

For a production macro, consider what should happen if the file cannot be opened. Workbooks.Open can fail for reasons such as a moved or inaccessible file, a password requirement, or a damaged workbook. A macro can use an error handler to report the problem, but it should not silently continue as if the file had opened. If you add error handling, make sure it restores any Excel settings the code changed and gives the user a useful explanation.

Common problems and how to fix them

A common mistake is assigning the result of GetOpenFilename directly to a workbook variable. The method returns a path, not a Workbook object. First capture the result in a Variant, check for cancellation, and then call Workbooks.Open. Another frequent issue is leaving out the cancellation check; in that case, the macro may try to open a value that is not a path.

The dialog appears, but nothing opens: Confirm that your code calls Workbooks.Open after the picker returns a path.
Cancel causes an error: Check for the Boolean result before passing the selection to Workbooks.Open.
The wrong files appear: Review the filter pattern and confirm that it includes the required workbook extensions.
The macro cannot find a worksheet: The chosen workbook may have a different sheet name or layout than the code expects. Check the workbook contents before referring to a specific sheet.
The chosen file opens read-only: It may already be in use, stored in a location with limited permissions, or marked read-only. The picker itself does not control those conditions.

Also distinguish the workbook that contains the macro from the workbook the user selects. ThisWorkbook refers to the file containing the running VBA code. ActiveWorkbook refers to the workbook that is currently active in Excel and may change as the macro opens files. For dependable code, use the object variable returned by Workbooks.Open when you mean the selected workbook, rather than relying on whichever workbook happens to be active.

Safety and workbook-opening options

Opening a workbook can have effects beyond displaying its sheets. It may contain external links, connections, formulas, or macros, and Excel’s security settings determine how some of those features are handled. Only select files from sources you trust, and do not treat a file filter as a security boundary. If a macro processes files from other people, explain which file it expects and avoid automatically enabling content just to make the workflow run.

Workbooks.Open accepts named arguments for options such as ReadOnly and UpdateLinks. For instance, a macro that only reads source data can choose to open a workbook read-only; a workflow that must preserve or update link behavior should make that choice deliberately. These options affect how the file is opened, not which path the dialog returns. Check the implications for your task before adding options, especially if the macro later saves or closes the selected workbook.

Do not assume a macro can be reversed with Ctrl+Z. Excel’s undo history does not undo VBA actions in the usual way. Save a backup copy before running a macro that changes workbook contents, and test changes on a sample file first. A picker macro that only opens a workbook is relatively limited, but later code may alter, overwrite, or close files.

Windows, Mac, and Excel for the web

The examples target desktop Excel for Microsoft 365 on Windows. On Windows, Alt+F11 opens the Visual Basic Editor; on a Mac, the editor is available through Tools > Macro > Visual Basic Editor, and the keyboard shortcut can vary with the keyboard and macOS settings. The file picker’s appearance and available locations follow the operating system, so the dialog may look different on a Mac even when the VBA approach is similar.

Excel for the web does not run VBA macros. A workbook containing VBA can be stored and viewed there, but you need desktop Excel to run this code. Excel for Mac supports VBA, though file dialog behavior and file paths can differ from Windows. If a macro will be used on both platforms, test the dialog and any path-dependent code on each platform rather than assuming a Windows path format will work on a Mac.

Frequently Asked Questions

Does Application.GetOpenFilename open the workbook?
No. It displays a selection dialog and returns a file path, or False if the user cancels. Call Workbooks.Open with the returned path to open the workbook.

How do I stop the macro when the user clicks Cancel?
Store the result in a Variant and test whether its type is Boolean before using it as a path. In the example above, the macro exits when VarType(selectedFile) = vbBoolean.

Can the dialog show only .xlsx files?
Yes. Change the GetOpenFilename filter to a pattern such as FileFilter:="Excel workbooks (*.xlsx),*.xlsx". The filter controls which matching files are shown; it does not convert other file types or guarantee that a selected workbook has a particular layout.

How can I open a workbook read-only?
After selecting a path, pass ReadOnly:=True to Workbooks.Open, for example, Workbooks.Open(Filename:=CStr(selectedFile), ReadOnly:=True). This requests a read-only opening; it does not change the source file’s permissions.

Can I use this VBA dialog in Excel for the web?
No. Excel for the web does not execute VBA. Run the macro in the desktop version of Excel; Excel for Mac can run VBA, with platform-specific differences in the dialog and file paths.

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.