Excel VBA Prompt to Open a File: Code and Options

VBA & macros · Updated November 19, 2026

To prompt someone to choose a workbook and then open it in Excel VBA, call Application.GetOpenFilename and pass the selected path to Workbooks.Open. The picker does not open the file on its own; your macro must check whether the user cancelled and then open the chosen workbook.

What the prompt does

A VBA file prompt displays a standard file-selection dialog so the person running the macro can browse to a file instead of typing a full path. This is useful when the workbook could be in different folders or have a different name each time. It also avoids assuming that every user has the same Windows account name or folder structure.

There are two common parts to the task: selecting a file and opening it. Excel’s Application.GetOpenFilename method handles selection and returns the chosen path as text. If the user clicks Cancel, it returns the Boolean value False. The Workbooks.Open method opens a workbook from a path. Keeping these steps separate makes it possible to respond properly to cancellation, validate the selection, or open the file with options such as read-only access.

Use this approach when the person should choose an existing file. If the goal is to ask for a new name and save a workbook, use a save dialog instead; that is a different task and should not be handled by opening the selected path.

Basic VBA code to select and open a workbook

In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, and paste the macro below into the module. On a Mac, use Developer > Visual Basic or the corresponding Visual Basic Editor command in Excel; the keyboard shortcut can depend on the keyboard and function-key settings. Save a copy of the workbook before running a macro: VBA changes cannot be undone with Ctrl+Z.

Sub PromptToOpenWorkbook()
    Dim selectedFile As Variant
    Dim openedBook As Workbook

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

    ' GetOpenFilename returns Boolean False if the user cancels.
    If VarType(selectedFile) = vbBoolean Then Exit Sub

    On Error GoTo OpenError

    ' Open the selected file read-only to avoid accidental edits.
    Set openedBook = Workbooks.Open(Filename:=CStr(selectedFile), ReadOnly:=True)
    MsgBox "Opened " & openedBook.Name, vbInformation
    Exit Sub

OpenError:
    MsgBox "Excel could not open the selected file." & vbCrLf & _
        Err.Description, vbExclamation
End Sub

The file filter limits the dialog to common Excel workbook extensions: .xlsx, .xlsm, and the older .xls. The filter is a convenience, not a guarantee that the selected file is valid or safe. Excel still checks the file when it tries to open it. The macro opens the workbook as read-only, which helps prevent accidental changes to the selected source. Remove ReadOnly:=True if the user is meant to edit the workbook, but consider whether saving changes to the source is appropriate.

The error handler catches an opening error, such as an inaccessible path or a damaged file, and displays Excel’s error description. It does not make every possible error harmless; it gives the macro a controlled response rather than stopping unexpectedly. For a production macro, you may want to log the error or provide more specific instructions.

Where to put the macro and how to run it

VBA code belongs in a standard module when you want to run it as a normal macro. In the Visual Basic Editor, use Insert > Module, then paste the procedure there. Return to Excel and run it from Developer > Macros, select PromptToOpenWorkbook, and click Run. On Windows, Alt+F8 opens the Macro dialog; on Mac, Option+F8 may open it depending on keyboard settings, and the Developer tab is another route.

If the Developer tab is not visible in Windows Excel, enable it through File > Options > Customize Ribbon, then select Developer. On Mac, the ribbon can be customized in Excel’s settings or preferences. When the workbook containing the macro needs to retain VBA, save it as an Excel Macro-Enabled Workbook (.xlsm), not as .xlsx; saving as a macro-free format removes its VBA project.

Excel for the web does not run VBA macros. Use the desktop Excel application to create and run this code. Desktop Excel for Mac supports VBA, but file-system permissions, dialog appearance, and keyboard shortcuts may differ from Windows. If a macro is distributed to other people, they need a desktop version of Excel that supports VBA and must follow their organization’s macro-security policy.

Using FileDialog instead

Another option is Excel’s Application.FileDialog with the file-picker type. It gives you a dialog object whose properties can be adjusted, including whether multiple files may be selected. For a single workbook, the following example provides a similar result to GetOpenFilename. Save a copy before running this macro as well, because VBA actions cannot be reversed with Ctrl+Z.

Sub PromptWithFileDialog()
    Dim picker As FileDialog
    Dim selectedPath As String
    Dim openedBook As Workbook

    ' Create a dialog for choosing an existing file.
    Set picker = Application.FileDialog(msoFileDialogFilePicker)
    With picker
        .Title = "Choose a workbook to open"
        .AllowMultiSelect = False
        .Filters.Clear
        .Filters.Add "Excel workbooks", "*.xlsx;*.xlsm;*.xls"

        ' Show returns -1 when the user selects a file and 0 on Cancel.
        If .Show <> -1 Then Exit Sub
        selectedPath = .SelectedItems(1)
    End With

    On Error GoTo OpenError
    Set openedBook = Workbooks.Open(Filename:=selectedPath, ReadOnly:=True)
    MsgBox "Opened " & openedBook.Name, vbInformation
    Exit Sub

OpenError:
    MsgBox "Excel could not open the selected file." & vbCrLf & _
        Err.Description, vbExclamation
End Sub

FileDialog is handy when you want to set dialog properties or later allow multiple selections. In this example, .AllowMultiSelect = False means only one file can be chosen, so the code reads .SelectedItems(1). If you change the setting to allow multiple selection, the macro must loop through the selected items and decide how to handle each workbook. It cannot simply treat the first selected path as the whole selection.

For a basic one-file picker, GetOpenFilename is usually shorter and direct. FileDialog makes the selection process more configurable. Neither method is a replacement for Workbooks.Open: the dialog selects a path, and the open method loads the workbook.

Choosing a file filter and opening options

A filter helps people find the intended file type by narrowing what is displayed in the picker. You can change the label and patterns to match the task. For example, if the macro should accept only comma-separated text files, use an appropriate CSV description and pattern. A filter should match the files the later code can actually process. It is also sensible to explain the expected file type in the dialog title or in a nearby instruction.

Opening a workbook has options that affect how Excel treats it. ReadOnly:=True opens it without intending to save edits to the source. If you omit that argument, Excel uses its normal behavior, which may allow editing. Other possible concerns include whether links should be updated and whether a password is required. Do not add options without understanding their effect: a workbook that depends on external links, for example, may display different values if links are not updated.

If users may select non-Excel files, do not assume that changing the dialog filter is enough. Confirm the file type and handle the possibility that the chosen content cannot be opened as a workbook. A file extension can be misleading, and opening a file can trigger security warnings or password prompts. For business workflows, specify which formats are supported and explain what to do if Excel rejects a file.

Common problems and fixes

  • The macro does nothing after Cancel. That is expected if the code exits when the picker returns False. This check is important: without it, the macro might pass an invalid value to Workbooks.Open.
  • Excel says the file cannot be found or opened. The file may have been moved, removed, renamed, blocked by permissions, or be an unsupported or damaged file. Try opening it manually in Excel and confirm that the person running the macro can access its location.
  • The macro is missing from the Macro dialog. Check that the procedure is in a standard module, is declared as a public macro (the example’s default is public), and that the workbook is saved in a macro-enabled format. A procedure requiring arguments may not appear as a runnable macro in the usual list.
  • Excel blocks the macro. Macro security settings or organizational policy may prevent it from running. Do not tell users to enable all macros or weaken security globally. They should follow their organization’s approved process and only run code from a source they trust.
  • The code works on one computer but not another. The selected folder, network access, cloud-storage permissions, and platform can differ. A picker avoids hard-coding a user-specific path, but it cannot grant access to a file the user is not authorized to read.

When debugging, first test the selection step by displaying the returned path after confirming that it is not Boolean False. Then test opening a known, accessible workbook. This separates a dialog problem from an access or file problem. Remove temporary diagnostic messages after the workflow is stable so users do not see unnecessary prompts.

Safety and reliable workbook handling

A file picker lets the user choose a location, but it does not establish that a file is trustworthy. Opening an unfamiliar workbook can expose the user to malicious content, misleading data, or external-link prompts. Keep Excel’s security protections enabled, and do not encourage users to enable content simply to make a macro continue. A selected macro-enabled workbook may contain its own VBA; decisions about enabling its macros should be made separately from the macro that opened it.

The example opens the chosen file read-only so the source is less likely to be overwritten accidentally. If your process must edit the selected workbook, consider checking whether it is already open, asking for confirmation before changing data, and making a backup through an approved workflow. Remember that Ctrl+Z does not undo changes made by VBA. Save a copy of the macro workbook before testing code, and test with sample files rather than important originals.

For a shared macro, describe what the code will open and whether it changes anything. Handle cancellation without an error, show a useful message when opening fails, and avoid storing sensitive paths or passwords in the code. If the selected workbook contains confidential information, follow the organization’s normal access and data-handling rules.

Frequently Asked Questions

How do I make Excel VBA ask the user to choose a file?
Use Application.GetOpenFilename to show the picker. It returns the selected path or Boolean False if the user cancels. Check the return value before using it.

Does GetOpenFilename open the workbook?
No. It only returns the selected file name or path. Pass the returned path to Workbooks.Open to open the workbook.

How do I let the user cancel without an error?
Test the return value before calling Workbooks.Open. With GetOpenFilename, a Boolean return indicates cancellation; with the FileDialog example, .Show returns 0 when the user cancels.

Can VBA open the selected workbook as read-only?
Yes. Set ReadOnly:=True in the Workbooks.Open call. This is useful when the macro only needs to read information and should not invite edits to the source file.

Can this file-picker macro run in Excel for the web?
No. Excel for the web does not run VBA. Open the macro-enabled workbook in desktop Excel for Windows or Mac to use VBA, subject to that installation’s security settings and permissions.

Should I use GetOpenFilename or FileDialog?
Choose GetOpenFilename for a concise prompt to select a file. Choose FileDialog when you need more control over the dialog or want to support selecting multiple files. In either case, the code must separately open the selected workbook.

Related

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.