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:

• Example 1
• Example 2, File Filters
• Example 3, Multiple Filters
• Example 4, Custom Title
• Example 5, Start Location

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

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:

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

Example 2, File Filters:

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

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:

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:

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:

The path selected is printed in cell A2:

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:

Example 4, Custom Title:

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

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:

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:

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

See also:

• VBA Save File Dialog, FileDialog(msoFileDialogSaveAs)
• VBA Excel, Writing to a Text File
• Excel VBA Open File Dialog
• VBA File and Folder Dialogs
• MSDN, Microsoft, Application.GetSaveAsFileName Method (Excel)

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