Excel VBA Save File Dialog, GetSaveAsFilename()

There may be times when you need to ask the user to select a location to save a file. This can be done using a save file dialog. Keep in mind that a save file dialogs doesn’t actually save anything. It only returns the full path the user has selected to save the file. There are several different methods for creating a save file dialog. One of the methods is explained in the article Excel VBA Save File Dialog, FileDialog(msoFileDialogSaveAs). The method explained there has some pros and cons:

Pros:

  • The FileDialog(msoFileDialogSaveAs)  is available for VBA in all office products

Cons:

  • You can’t apply a filter to the file types.

The method I will explain in this article uses the command, GetSaveAsFilename(). This method also has some pros and cons:

Pros

  • You can apply  filters to the file types.

Cons

  • It is only available for VBA in Excel

Jump To:

You can download the file and code related to this article here.


Contents

Example 1:

In this example a dialog will open asking the user to select a location to save the file. The path selected will be printed in cell A2. Note the save file dialog will not actually save the file. It will only provide the full path the user has selected to save the file:

Sub Example1()
Dim varResult As Variant
'displays the save file dialog
varResult = Application.GetSaveAsFilename
'checks to make sure the user hasn't canceled the dialog
If varResult <> False Then
    Cells(2, 1) = varResult
End If

End Sub

Result:

Excel VBA, GetSaveAsFileName, Example

The user selected the directory “D:Temp” and the name “Test”. The full path can be seen in cell A2:

Excel VBA, GetSaveAsFileName, Example Result


Example 2, File Filters:

There was a problem with Example 1. There were no file filters to choose from:

Excel VBA, GetSaveAsFileName, Example 1 Error

The result path was “D:TempTest.”. A path with no file extension. If you try to save an excel file using that path you will end up with an unrecognized file type:

Excel, VBA Unrecognized File Type
In order for this not to happen you have 2 options:

Option 1: Ask the user to input the file extension every time he decides to save a file:

Excel VBA, GetSaveAsFileName, Manual File Extension INput
This would be very annoying on the users side.

Option 2: Apply filters to the save file dialog.

The code below opens a save file dialog with the .xlsx filter. Note the save file dialog will not actually save the file. It will only provide the full path the user has selected to save the file:

Sub Example2()
Dim varResult As Variant
'displays the save file dialog
varResult = Application.GetSaveAsFilename( _
    FileFilter:="Excel Files (*.xlsx), *.xlsx")
'checks to make sure the user hasn't canceled the dialog
If varResult <> False Then
    Cells(2, 1) = varResult
End If
End Sub

Result:

Excel VBA, GetSaveAsFileName, Example 2
The path selected is printed in cell A2:

Excel, VBA, GetSaveAsFileName, Example 2, Selected Path
As you can see this time the .xlsx extension was added to the file path.


Example 3, Multiple Filters:

Sometime you might want to give the user the option to choose the file extension from multiple available extensions. The example below creates 2 filters a .xlsx and a .xlsm:

Sub Example3()
Dim varResult As Variant
'displays the save file dialog
varResult = Application.GetSaveAsFilename(FileFilter:= _
    "Excel Files (*.xlsx)," & "*.xlsx, Macro Enabled" & _
    "Workbook (*.xlsm), *xlsm")
'checks to make sure the user hasn't canceled the dialog
If varResult <> False Then
    Cells(2, 1) = varResult
End If
End Sub

Result:

Excel VBA, GetSaveAsFileName, Example 3, Multiple Filters


Example 4, Custom Title:

The default title for the save file dialog is “Save As”:

Excel VBA, GetSaveAsFileName, Example Title
The code below changes the dialogs title to “Some Random Title”:

Sub Example4()
Dim varResult As Variant
'displays the save file dialog
varResult = Application.GetSaveAsFilename(FileFilter:= _
"Excel Files (*.xlsx),*.xlsx, Macro Enabled Workbook " & _
"(*.xlsm), *xlsm", Title:="Some Random Title")
'checks to make sure the user hasn't canceled the dialog
If varResult <> False Then
    Cells(2, 1) = varResult
End If
End Sub

Result:Excel VBA, GetSaveAsFileName, Example 3, Title


Example 5, Start Location:

You can tell the dialog to start in a specific folder. In the example below the save file dialog will start in the directory “D:TempFolder to Start”:

Sub Example5()
Dim varResult As Variant
'displays the save file dialog
varResult = Application.GetSaveAsFilename(FileFilter:= _
    "Excel Files (*.xlsx), *.xlsx, Macro Enabled Workbook" & _
    "(*.xlsm), *xlsm", Title:="Some Random Title", _
    InitialFileName:="D:TempFolder to Start")
'checks to make sure the user hasn't canceled the dialog
If varResult <> False Then
    Cells(2, 1) = varResult
End If
End Sub

Result:

Excel VBA, GetSaveAsFileName, Example 5, Start location

You can download the file and code related to this article here.

See also:

If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website  www.software-solutions-online.com

6 thoughts on “Excel VBA Save File Dialog, GetSaveAsFilename()”

Leave a Reply

Your email address will not be published. Required fields are marked *