The Excel VBA File Save As dialog is opened with the Application.GetSaveAsFilename method, which displays the standard Windows Save As dialog and returns the chosen file path as a string without actually saving the file. To both show the dialog and save the file, you use ActiveWorkbook.SaveAs with the FileName argument set to the dialog’s return value.
Why Use a Save As Dialog in VBA?
When you automate Excel with VBA, you often need to let the user choose where and under what name to save a file. Hard-coding a path like C:\Reports\data.xlsx works only for that one location. The Save As dialog gives the user control, making your macro flexible and safe—it prevents accidental overwrites because the user can see and confirm the destination.
The dialog is part of the Microsoft Office object model, not a custom VBA form. It looks and behaves exactly like the native Excel Save As window, so users already know how to interact with it.
How to Open the Save As Dialog (Two Ways)
There are two distinct methods, and they do different things:
Application.GetSaveAsFilename– shows the dialog and returns the full path as a string. It does not save the file. You must then useSaveAsseparately.Workbook.SaveAs– this method can also display the Save As dialog if you omit theFileNameargument, but it immediately saves the file once the user clicks Save.
Most programmers prefer GetSaveAsFilename because it gives you a chance to validate the path or modify the filename before actually saving.
Basic Example: GetSaveAsFilename
Here is a simple macro that opens the dialog and shows the chosen path in a message box:
Sub ShowSaveAsDialog()
Dim filePath As String
' Display the dialog with a default name and Excel filter
filePath = Application.GetSaveAsFilename( _
InitialFileName:="MyReport.xlsx", _
FileFilter:="Excel Workbook (*.xlsx), *.xlsx")
' If user cancels, filePath is False (a boolean)
If filePath = "False" Then
MsgBox "You cancelled the dialog."
Else
MsgBox "You chose: " & filePath
End If
End Sub
Note that GetSaveAsFilename returns the string "False" when the user clicks Cancel. In VBA, this is a string, not a boolean, so you compare it to the text "False".
How to Actually Save the File After the Dialog
Once you have the path from GetSaveAsFilename, you pass it to SaveAs:
Sub SaveWithDialog()
Dim filePath As String
filePath = Application.GetSaveAsFilename( _
InitialFileName:="MyReport.xlsx", _
FileFilter:="Excel Workbook (*.xlsx), *.xlsx")
If filePath = "False" Then Exit Sub
' Save the active workbook to the chosen path
ActiveWorkbook.SaveAs Filename:=filePath, _
FileFormat:=xlOpenXMLWorkbook
MsgBox "File saved to: " & filePath
End Sub
In this example, FileFormat:=xlOpenXMLWorkbook forces the .xlsx format. If you omit it, Excel uses the default format for the current workbook. Always save a copy of your workbook before running a macro that saves files, because macros cannot be undone with Ctrl+Z.
Customizing the Dialog: Filters, Default Name, and Title
You can control several aspects of the dialog:
- InitialFileName – sets the default filename and folder. You can include a full path like
"C:\Reports\MyReport.xlsx". - FileFilter – defines the file type dropdown. Use the pattern
"Description, *.ext". Separate multiple filters with commas:"Excel Files (*.xlsx), *.xlsx, CSV Files (*.csv), *.csv". - Title – changes the dialog window title (e.g.,
Title:="Save Your Report"). - ButtonName – on Windows, you can change the text of the Save button (e.g.,
ButtonName:="Export"). This is not available on Mac.
Here is an example with all options:
Sub CustomSaveDialog()
Dim filePath As String
filePath = Application.GetSaveAsFilename( _
InitialFileName:="C:\Reports\Q1_Report.xlsx", _
FileFilter:="Excel Workbook (*.xlsx), *.xlsx, CSV (*.csv), *.csv", _
Title:="Choose a Destination", _
ButtonName:="Export")
If filePath = "False" Then Exit Sub
ActiveWorkbook.SaveAs Filename:=filePath
End Sub
Common Problems and How to Avoid Them
Several issues trip up beginners:
- Cancel detection – As noted,
GetSaveAsFilenamereturns the string"False"when cancelled. Some code incorrectly checksIf filePath = False(without quotes), which never works because VBA converts the boolean to a string anyway. Always compare to"False". - Overwriting existing files – The dialog shows a confirmation prompt if the file exists, but if you use
SaveAsdirectly without the dialog, it overwrites silently. Always use the dialog when user input is involved. - Invalid characters in filename – Windows forbids
\ / : * ? " < > |. If the user types an invalid name, the dialog itself prevents it, but if you pass a hard-coded name, you must validate it. - Mac differences – On Excel for Mac,
GetSaveAsFilenameworks, but theButtonNameargument is ignored. Also, the dialog may look slightly different, but the functionality is the same.
Alternatives to the Native Dialog
If you need more control, you can build a custom UserForm with a text box and a Browse button. However, that requires more code and does not give the familiar native experience. Another alternative is the FileDialog object with msoFileDialogSaveAs, but that is essentially the same as GetSaveAsFilename.
For saving without any user interaction, you can use ActiveWorkbook.SaveAs with a hard-coded path, but that is risky because it can overwrite files without warning.
Frequently Asked Questions
Why does my code say “False” when I check if the user cancelled?
Because GetSaveAsFilename returns the string "False", not a boolean. You must compare it to the text "False" (with quotes). If you compare to False without quotes, VBA converts it to a string anyway, but it’s clearer to use quotes.
Can I use the dialog to save as CSV or PDF?
Yes. Set the FileFilter to include those types, but note that GetSaveAsFilename only returns a path. To save as CSV, you must use ActiveWorkbook.SaveAs FileFormat:=xlCSV. For PDF, you use ExportAsFixedFormat instead of SaveAs.
How do I make the dialog remember the last used folder?
By default, the dialog opens in the last folder used by Excel. If you want to set a specific default folder, provide a full path in InitialFileName.
Does the dialog work in Excel for the web?
No. VBA is not supported in Excel for the web. You must use the desktop version of Excel (Windows or Mac) to run macros that use GetSaveAsFilename.
Can I prevent the user from changing the file extension?
The dialog does not have a built-in lock. You can check the returned path in your code and force an extension if needed, but the user can type any extension they want.
Safety Note
Always save a backup copy of your workbook before running any macro that saves files. Macros cannot be undone with Ctrl+Z. If your macro overwrites an important file, you could lose data permanently. Test your macro on a copy of the workbook first.
Related
- Excel Template for Paying Off Debt: Free & DIY
- Excel Template for Personal Expense Tracking
- Excel Template for Project Budget: How to Build One
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Intersect: Check Whether Cell Ranges OverlapIn Excel VBA, use Application.Intersect to find the cells shared by two or more ranges on the same…
- VBA Error Handler: How On Error Works and When to Use ItIn VBA, an error handler is code that responds when a statement fails while a procedure is running.…
- Excel VBA: Add Double Quotes to a String in VBA CodeTo add double quotes around a string in Excel VBA, concatenate a quote character before and after the…
- VBA Find Last Used Column in Excel: Reliable MethodsTo find the last used column anywhere on an Excel worksheet with VBA, use Range.Find with SearchOrder:=xlByColumns and…
- Google Sheets Macro Converter: What It Does and LimitsA Google Sheets macro converter can help translate some Excel VBA macros into Google Apps Script, but it…
- Excel VBA Dictionary Add: Syntax, Examples, and TipsIn Excel VBA, add an entry to a Dictionary with dict.Add key, item, after creating the Dictionary object.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.