[Excel VBA] How to Show the “Save As” Dialog and Get the File Path (GetSaveAsFilename)

When saving reports created with macros, you might want to let the user decide the file name, save location, and file type (such as xlsx or pdf) instead of using a fixed name.

By using the Application.GetSaveAsFilename method in VBA, you can display the “Save As” dialog box and retrieve the save path entered by the user as a text string.

In this article, I will explain the basic usage of this method and how to apply it to actually save a file.

The Completed VBA Code

Sub ShowFileSaveAsDialog()

    ' Declare variables. 
    ' Use Variant type because the return value can be a path (String) or False (Boolean).
    Dim saveFilePath As Variant
    Dim initialFileName As String
    
    '--- 1. Set initial dialog values ---
    ' Propose a default file name in advance
    initialFileName = ThisWorkbook.Path & "\Report_" & Format(Date, "yyyymmdd") & ".xlsx"

    '--- 2. Show the "Save As" dialog box ---
    saveFilePath = Application.GetSaveAsFilename( _
        InitialFileName:=initialFileName, _
        FileFilter:="Excel Workbook (*.xlsx),*.xlsx,CSV (Comma delimited) (*.csv),*.csv", _
        Title:="Please select a location to save the report")
        
    '--- 3. Check the selection result ---
    If saveFilePath = False Then
        ' If "Cancel" was pressed
        MsgBox "Save was canceled."
    Else
        ' If a file path was specified
        MsgBox "The following path was selected for saving:" & vbCrLf & saveFilePath
    End If

End Sub

Explanation of Key Points

1. Application.GetSaveAsFilename Method

When you execute this method, the “Save As” dialog appears. The most important point is that this method does not actually save the file. It only returns the full path specified by the user as a string.

2. InitialFileName Argument

You can specify a default file name or path to be displayed in the file name input field when the dialog opens. This is a helpful setting to save the user time.

3. FileFilter Argument

This sets the contents of the “Save as type” dropdown list in the dialog. It uses the same format as GetOpenFilename, specifying pairs of “Display Name, *.Extension” separated by commas.

4. Handling Cancellation

If saveFilePath = False Then

If the user clicks the “Cancel” button in the dialog, this method returns the Boolean value False. By declaring the variable receiving the return value as Variant and checking if it is False using an If statement, you can correctly handle the cancellation.

[Advanced] Actually Saving the Workbook to the Specified Path

By combining the file path obtained with GetSaveAsFilename and the SaveAs method, you can complete the process of “saving a file with the name and location chosen by the user.”

Sub GetPathAndSaveFile()
    Dim savePath As Variant
    
    ' 1. First, get only the "save path" using GetSaveAsFilename
    savePath = Application.GetSaveAsFilename(InitialFileName:="NewReport.xlsx")
    
    ' 2. If not canceled, actually save the workbook using the retrieved path
    If savePath <> False Then
        ' As an example, save this macro workbook with the new name
        ThisWorkbook.SaveAs Filename:=savePath
        
        MsgBox "Workbook saved to: " & savePath
    Else
        MsgBox "Save was canceled."
    End If
End Sub

Summary

The Application.GetSaveAsFilename method is an essential feature for creating flexible macros that leave the save location up to the user.

• The return value is the full file path (String), and it does not perform the save action itself.
• You can suggest a recommended file name using the InitialFileName argument.
• You check if the return value is False to determine if the operation was canceled.
• To actually save the file, you must pass the retrieved path to the SaveAs method.

By mastering these points, you can create interactive and user-friendly VBA tools.