To show a Save As dialog from Excel VBA, call Application.GetSaveAsFilename, check whether the user cancelled, and then pass the chosen path to Workbook.SaveAs. The dialog only asks for a filename; it does not save the workbook by itself.
Two ways to show Save As from VBA
There are two commonly useful approaches, and they do different jobs:
Application.GetSaveAsFilenameasks the user to choose a name and location, then returns the selection to your macro. Your code decides what to do with that path.Application.Dialogs(xlDialogSaveAs).Showopens Excel’s built-in Save As dialog and carries out the save through the dialog. It is a shorter route when you want Excel to manage the normal save interaction.
Use the first approach when your macro needs to validate the filename, choose a file format, or perform additional work before saving. Use the built-in dialog when the aim is simply to let the user save interactively. Do not confuse either dialog with Workbook.SaveAs: that method saves to a specified filename and does not, on its own, ask the user where to save.
Working example: ask for a path and save as XLSM
This example targets the active workbook, displays a dialog filtered for macro-enabled workbooks, exits cleanly if the user presses Cancel, and saves the chosen workbook as an XLSM file. Save a copy of the workbook before running any macro: VBA changes cannot be undone with Ctrl+Z.
Sub SaveActiveWorkbookAsXlsm()
Dim wb As Workbook
Dim selectedPath As Variant
' Target the workbook currently selected in Excel.
Set wb = ActiveWorkbook
' Ask for a destination; this step does not save the workbook.
selectedPath = Application.GetSaveAsFilename( _
InitialFilename:="Report.xlsm", _
FileFilter:="Excel Macro-Enabled Workbook (*.xlsm), *.xlsm", _
Title:="Save workbook as")
' Cancel is returned as the Boolean value False.
If VarType(selectedPath) = vbBoolean Then Exit Sub
' Ensure the saved name has the extension that matches the format.
If LCase$(Right$(CStr(selectedPath), 5)) <> ".xlsm" Then
selectedPath = CStr(selectedPath) & ".xlsm"
End If
' Save in Excel's macro-enabled workbook format.
wb.SaveAs Filename:=CStr(selectedPath), _
FileFormat:=xlOpenXMLWorkbookMacroEnabled
End Sub
The filter limits the file types offered in the dialog, while the FileFormat argument tells Excel which format to write. Matching the extension and format matters: a filename ending in .xlsm should be saved with the macro-enabled format, not merely given that suffix. The code appends .xlsm if the user omits an extension. If the user types a different suffix, it appends .xlsm rather than silently claiming that the other suffix represents an XLSM file.
How the dialog and cancellation check work
GetSaveAsFilename returns a Variant. When the user chooses a location, that value contains the selected filename as text; when the user cancels, the return value is Boolean False. Checking the value’s type avoids treating a path as though it were a Boolean. After a valid selection, the macro can inspect or modify the path before calling a save method.
InitialFilename supplies a suggested name, not a command to save there. You can provide a full path to suggest a starting folder, or just a filename such as Report.xlsm. FileFilter is written as comma-separated description and pattern pairs. For example, a filter can offer “Excel Workbook (*.xlsx)” with the pattern *.xlsx. Title changes the dialog’s caption. The user can still navigate to a different folder.
After the user selects a path, wb.SaveAs performs the actual save. Saving with SaveAs changes the workbook’s name and location in the current Excel session; it is not simply a detached duplicate. If you need to keep the open workbook under its existing name and create a separate copy, consider Workbook.SaveCopyAs. That method saves a copy but does not offer the same format-conversion control as SaveAs.
Choose the right workbook format
Excel’s filename extension and file format should agree. The main choices relevant to a VBA save dialog are:
- .xlsx: A standard Excel workbook that does not store VBA macros. Saving a workbook containing VBA as .xlsx can remove its VBA project, so do not choose it when the saved file must retain its macros.
- .xlsm: An Excel macro-enabled workbook. Use
xlOpenXMLWorkbookMacroEnabledwith this extension when the saved file needs to keep VBA code. - .xlsb: An Excel binary workbook, which can also store macros. Use the corresponding Excel file-format constant if you deliberately need this format.
- .xls: The older Excel 97–2003 workbook format. Choose it only when compatibility with older software is required; it has older format limitations.
A common misconception is that typing .xlsm at the end of a filename makes an ordinary .xlsx save macro-enabled. The suffix does not determine the file’s internal format; the FileFormat argument does. Likewise, choosing an XLSM filter does not save anything until the macro calls SaveAs.
Use the built-in Excel Save As dialog instead
If you want Excel’s standard dialog to handle the save directly, a macro can show the built-in Save As dialog with Application.Dialogs(xlDialogSaveAs).Show. This is useful when users should choose the format and interact with Excel’s familiar save options. However, it gives your macro less opportunity to validate a returned path before saving because the dialog itself performs the operation. For custom rules—such as forcing a particular extension or checking a folder—GetSaveAsFilename followed by an explicit SaveAs call is generally easier to control.
Also distinguish Save As from Save a Copy. Save As changes the identity of the open workbook after saving. A copy operation is more suitable if the original should remain the active workbook. Test the workflow on a disposable copy, especially if the macro is expected to create a different format or overwrite an existing file.
Windows, Mac, and Excel for the web
The code above is intended for Excel for Microsoft 365 on Windows. Excel for Mac supports VBA, but dialogs and file-system behavior can differ from Windows; the visual appearance and available file types may not be identical. When writing cross-platform code, Application.PathSeparator is preferable to hard-coding a backslash when you build paths. The example avoids constructing a full folder path and therefore uses a simple suggested filename.
Excel for the web does not run VBA macros. A user can open and edit many Excel workbooks in a browser, but a VBA Save As macro must be run in the desktop Excel application. The user’s operating-system keyboard shortcut for Save As is not needed by this code: the dialog is opened programmatically. On Windows, Ctrl+S saves the current workbook rather than invoking this custom selection flow; Mac keyboard shortcuts differ, but do not change how the VBA method works.
Common problems and how to avoid them
- The workbook is not saved after choosing a name: That is expected if the macro only calls
GetSaveAsFilename. Add aSaveAscall after checking for Cancel. - Cancel causes an error: Handle the Boolean False return before converting the result to a string or passing it to SaveAs.
- The extension and contents disagree: Set both the filename extension and the correct
FileFormatconstant. A filter is not a replacement for the format argument. - Macros disappear: The destination format may be .xlsx. Save as .xlsm or another macro-capable format if the saved workbook must contain VBA.
- The wrong workbook is saved:
ActiveWorkbookmeans the workbook currently active in Excel;ThisWorkbookmeans the workbook that contains the running macro. If the macro is stored in PERSONAL.XLSB or an add-in, those references point to different workbooks. Set the target explicitly and confirm it before saving. - An existing file prompts for confirmation: Excel may ask whether to replace it. Do not disable alerts casually: doing so can suppress important warnings, including overwrite or format messages.
Frequently Asked Questions
How do I open a Save As dialog in Excel VBA?
Call Application.GetSaveAsFilename to ask the user for a destination. Check whether the return value is Boolean False, then call the chosen workbook’s SaveAs method with the path and file format.
Does GetSaveAsFilename save the workbook?
No. It displays a dialog and returns a filename or False if the user cancels. Your macro must call Workbook.SaveAs or another appropriate save method to write a file.
How do I detect Cancel in a Save As dialog?
Store the return value in a Variant and check whether its type is Boolean before treating it as a path. For example, the sample exits when VarType(selectedPath) = vbBoolean; cancellation is returned as False.
How can I save a macro-enabled workbook from VBA?
Use an .xlsm filename and specify xlOpenXMLWorkbookMacroEnabled as the SaveAs FileFormat. A file extension alone does not preserve macros if the workbook is saved in a macro-free format.
Can I use this VBA dialog in Excel for the web?
No. Excel for the web does not execute VBA macros. Open the workbook in the desktop version of Excel to run a VBA Save As dialog.
Related
- Excel Unmerge Cells and Fill Every Cell with Its Data
- Excel Undo Shortcut: Ctrl+Z and How It Works
- Excel UK Number Format: Dates, Currency and Separators
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- How to List All Files in a Folder Using Excel VBATo list files directly inside a folder in Excel for Microsoft 365 on Windows, run a VBA macro…
- Excel VBA Next Without For Error: Causes and FixesThe Excel VBA compile error “Next without For” means VBA has reached a Next statement without finding a…
- Excel VBA Error Codes: Meanings and FixesExcel VBA error codes identify problems that stop a macro or signal that an operation failed. The most…
- VBA Loop Through Cells: Methods and ExamplesTo loop through cells in Excel VBA, use For Each for a specified range or a counter-based For…
- VBA Code to Display a Graph on a UserFormUse an Excel chart on a worksheet as the graph source, export it as a JPEG, then load…
- Excel VBA Reference: Where to Find It and How to Use ItAn Excel VBA reference can mean either Microsoft’s documentation for Excel’s VBA object model or a library that…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.