In Excel VBA, use Application.GetOpenFilename to display a file-selection dialog and Application.GetSaveAsFilename to ask where a file should be saved. Neither method opens or saves a workbook by itself: after the user chooses a file, your macro must call Workbooks.Open or Workbook.SaveAs.
What the VBA file dialog does
A dialog box lets someone choose a file through Excel’s familiar file browser instead of typing a full path into a macro. This is useful when the workbook’s location varies, when a macro needs to process a file supplied by the user, or when someone should choose a destination for an exported workbook.
There are two separate actions to keep straight. Showing a dialog collects a choice; the follow-up VBA statement performs the operation. For example, GetOpenFilename returns a selected path, but the workbook is not opened until the code passes that path to Workbooks.Open. Likewise, GetSaveAsFilename returns a proposed path, but the workbook is not written until the code calls a save method.
These methods are available in desktop Excel for Microsoft 365 on Windows and Mac. Excel for the web does not run VBA macros. The dialog’s appearance and path format can differ between Windows and Mac, but the basic VBA pattern is similar.
Choosing the right method
Application.GetOpenFilenameis a straightforward choice when you want the user to select a file and then your macro will open or process it.Application.GetSaveAsFilenameis useful when you want the user to choose a filename and destination, then your code will save a workbook there.Application.FileDialogoffers more control over a picker, including file filters and multi-selection for a file picker. It can also show folder-selection and Save As dialogs.Application.Dialogs(xlDialogOpen).Showdisplays Excel’s built-in Open dialog and can be useful for a simple, Excel-native interaction. It gives your code less direct control over the returned selection than the filename methods do.
For most macros, start with GetOpenFilename or GetSaveAsFilename. They make the important choice visible in the code: the dialog returns a value, and the macro decides what to do with it.
Open a workbook with GetOpenFilename
The following macro asks the user to choose an Excel workbook, exits cleanly if the user cancels, and opens the selected file. Before running a macro, save a separate copy of your workbook: VBA changes cannot be undone with Ctrl+Z.
Sub ChooseAndOpenWorkbook()
Dim selectedFile As Variant
' Ask the user to choose an Excel workbook.
selectedFile = Application.GetOpenFilename( _
FileFilter:="Excel workbooks (*.xls*),*.xls*", _
Title:="Select a workbook to open", _
MultiSelect:=False)
' Cancel returns Boolean False rather than a file path.
If VarType(selectedFile) = vbBoolean Then
If selectedFile = False Then Exit Sub
End If
' Open the path returned by the dialog.
Workbooks.Open Filename:=CStr(selectedFile)
End Sub
The result is declared as a Variant because the method can return either a filename or the Boolean value False. Checking for cancellation before calling Workbooks.Open prevents the macro from trying to open a nonexistent path. The filter shown allows common Excel workbook extensions; you can narrow it to a particular format if the task requires one.
With MultiSelect:=False, the result is one filename. If you set MultiSelect:=True, the method returns an array of selected filenames, so the macro must loop through that array rather than pass the result directly to one Workbooks.Open call. The method can also change Excel’s current drive or folder, so avoid relying on the working directory remaining unchanged.
Ask where to save a workbook
Use GetSaveAsFilename when the user should choose a destination and name. The example below offers an XLSM filename and then saves the active workbook as a macro-enabled workbook. It saves the workbook itself; it is not merely exporting a worksheet.
Save a copy of the workbook before trying this macro. A successful SaveAs changes the workbook’s saved name and location, and changes made by VBA are not reversible with Ctrl+Z.
Sub ChooseSaveAsLocation()
Dim wb As Workbook
Dim savePath As Variant
' Capture the workbook that is active when the macro starts.
Set wb = ActiveWorkbook
' Ask for a destination and suggest an XLSM filename.
savePath = Application.GetSaveAsFilename( _
InitialFileName:="Report.xlsm", _
FileFilter:="Excel Macro-Enabled Workbook (*.xlsm),*.xlsm", _
Title:="Save workbook as")
' Cancel returns Boolean False.
If VarType(savePath) = vbBoolean Then
If savePath = False Then Exit Sub
End If
' Save the chosen workbook in macro-enabled format.
wb.SaveAs Filename:=CStr(savePath), _
FileFormat:=xlOpenXMLWorkbookMacroEnabled
End Sub
The FileFormat argument matters: an XLSM extension and macro-enabled file format belong together. Saving a macro-containing workbook as a standard XLSX file can remove its VBA project. If your macro is stored in an add-in or Personal Macro Workbook, ActiveWorkbook may not be the workbook you intend to save. In that situation, set wb explicitly to the target workbook rather than assuming which workbook is active.
Filters, filenames, and multi-selection
A file filter helps users find appropriate files, but it is not a validation rule for your data. For GetOpenFilename, the filter string pairs a description with a pattern, separated by a comma, as in "CSV files (*.csv),*.csv". To offer more than one choice, provide additional description-and-pattern pairs separated by commas. The dialog’s title can explain what the macro expects, such as “Choose the monthly source file.”
For a save dialog, InitialFileName supplies a suggested name or path; it does not force the user to accept it. A filter can suggest a format, but your macro should still use an appropriate FileFormat when it calls SaveAs. If users can choose multiple formats, make the selected extension and the save format agree instead of always writing one format behind a different-looking extension.
With multi-selection enabled for an open picker, check whether the return value is Boolean False before treating it as an array. When it is an array, loop over its elements and handle each path individually. Also decide whether the macro should open files that are already open, skip them, or report an error; the dialog does not make that decision for you.
Using Application.FileDialog instead
Application.FileDialog is useful when a macro needs a more configurable picker. For a file picker, the basic sequence is to create a dialog, set its title and filters, show it, and read the selected item only if the user confirms. The SelectedItems collection contains the chosen paths. A Save As dialog can also be requested with the appropriate file-dialog type, but the code still has to perform the actual save.
This option can make sense when you want a multi-select picker or need to configure several dialog properties in one place. It introduces more setup than GetOpenFilename, however, and it does not remove the need to handle cancellation. For simple one-file selection, the shorter filename method is often easier to read and maintain. On Mac, the picker is presented using the platform’s interface, so its appearance may not match Windows exactly.
Common problems and safe handling
- The macro errors after Cancel: Check for the Boolean return value before using the result as a path. A cancelled selection is not an empty filename.
- The macro opens the wrong workbook: Identify the target workbook explicitly.
ActiveWorkbookmeans the workbook currently active when that line runs, whileThisWorkbookmeans the workbook containing the VBA code; those can be different. - The saved file loses macros: Use the macro-enabled workbook format for an XLSM file. An XLSX file does not preserve a VBA project.
- A path works on Windows but not Mac: Don’t assemble paths by assuming a Windows drive letter or separator. Use the path returned by the dialog, and account for platform-specific paths if your macro builds paths itself.
- The selected file cannot be opened or saved: It may have been moved, permissions may prevent access, or another application may lock it. Handle errors deliberately and give the user a useful message rather than assuming every chosen path succeeds.
For safer automation, consider whether opening should be read-only, whether a file may contain macros, and whether an existing destination should be overwritten. The dialog is a user-interface convenience, not a security check. Only enable macros in files you trust, and avoid silently overwriting valuable work.
Keyboard shortcuts and dialog alternatives
If a person is opening or saving a workbook manually rather than running a macro, Excel already provides standard commands. In Excel for Microsoft 365 on Windows, Ctrl+O opens the Open page, Ctrl+S saves, and F12 opens Save As. On Mac, Command+O opens a file, Command+S saves, and Command+Shift+S opens Save As. The precise screens can vary with Excel’s settings and platform.
These shortcuts do not replace a VBA dialog when a macro must collect a path and continue processing automatically. Conversely, if the user only needs to open or save the workbook they are already working in, a macro may be unnecessary. Choose VBA when the selected file or destination is an input to a repeatable task, not just to reproduce a normal Excel command.
Frequently Asked Questions
How do I open a file dialog in Excel VBA?
Call Application.GetOpenFilename to let a user select a file. Check whether the return value is Boolean False for Cancel, then pass a valid returned path to Workbooks.Open if you want to open it.
Does GetSaveAsFilename save the workbook?
No. It displays a dialog and returns the proposed filename or False if the user cancels. Your macro must call Workbook.SaveAs to save the workbook.
How do I detect when the user clicks Cancel?
For these filename methods, cancellation returns the Boolean value False. Store the result in a Variant and check its type and value before treating it as a path.
Can a VBA open dialog select more than one file?
Yes. Set MultiSelect:=True with GetOpenFilename. The successful result is then an array of filenames, which your code needs to process one path at a time.
Can Excel for the web run this VBA dialog?
No. Excel for the web does not execute VBA macros. Use desktop Excel for a VBA-based picker; a macro-enabled workbook can still be stored online, but it must be opened in a compatible desktop application to run its VBA.
Related
- Supply List Excel Template: How to Build and Use One
- Excel Superscript Shortcut: Apply Superscript to Text
- Subscript vs Superscript in Excel: What’s the Difference?
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel Solver VBA: Set Up and Run Optimization ModelsYes—Excel Solver can be controlled with VBA to set an objective, choose changing cells, add constraints, and run…
- Excel VBA Dictionary: Add Items and Handle Existing KeysIn Excel VBA, add a key-item pair to a dictionary with its .Add method, such as dict.Add "North",…
- Excel Macro Keyboard Shortcuts: Assign and Run MacrosIn Excel for Microsoft 365 on Windows, assign a macro shortcut by choosing Developer > Macros, selecting the…
- Excel VBA: How to Assign a Keyboard Shortcut to a MacroTo assign a keyboard shortcut in Excel for Microsoft 365 on Windows, press Alt+F8, select the macro, choose…
- Excel VBA: Get the First Item in a DictionaryTo get the first key from a VBA Scripting.Dictionary, copy its keys into a Variant array and read…
- Excel VBA InputBox With Multiple InputsExcel VBA’s built-in InputBox and Application.InputBox each display one input field, not a form with several fields. To…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.