Using Application.GetSaveAsFilename in VBA in Excel (Examples)

If you are coding in VBScript, Excel and you need to extract the filename that a user uses when saving a file, the GetSaveAsFilename() method can be useful for you.

This can be quite helpful when you want the end-user to select a file from their required file location using a GUI like a Save as dialog box. 

In this tutorial, we will demonstrate with a few coding examples how to use the Application.Getsaveasfilename method in Excel

What does the GetSaveAsFilename Method do in Excel?

The GetSaveAsFilename() method in Excel does two things:

  • Displays a customized Save As dialog box
  • Extracts the filename the user specifies in the ‘Filename’ input field of the dialog box.

It does not, however, actually save the file even when the user presses the Save button. You will need to use a separate function if you want the file to be saved.

Now what do we mean by a ‘customized Save As dialog box’ ?

This means you can specify what title you want in the Save As dialog box, as well as specify an initial filename (that the user can change according to his/ her requirement).

You can also specify filters for the types of files the user will be allowed to save as. For example, you might want to give the user options to save as only text (.txt) and Excel (.xlsx) files.

It is important to note here that the GetSaveAsFilename() method may change the current drive or folder.

Syntax for the GetSaveAsFilename Method in Excel

The syntax for the GetSaveAsFilename() method is as follows:

expression.GetSaveAsFilename (InitialFilename, FileFilter, FilterIndex, Title)

Here, expression is an application object variable.

The arguments of this function are all optional. Here’s what they represent:

  • InitialFilename is the default filename that appears in the ‘Filename’ input field when the dialog box appears. It is usually used to provide the user with a suggested file name. If this argument is omitted, Microsoft Excel uses the name of the active workbook.
File name in the save as dialog box
  • FileFilter is a string with which you can specify the file filtering criteria, like which types of files the user will be allowed to save the file with. This string can have a maximum of 255 characters.
  • FilterIndex is the number of file filters that you want to use.
  • Title is the title you want to be displayed in the title bar of the Save As dialog box. If this argument is omitted, the default title ‘Save As’ is used.
Title to show in the save as dialog box

Example VBA Codes Using GetSaveAsFilename in Excel

Let us take a look at some example codes to understand how the GetSaveAsFilename method is used and applied.

VBA Code to Get Filename with Path from a Save As Dialog Box

The first sample code lets you extract the file name specified by the user in the ‘Filename’ field of the Save As dialog box and then use it to display the name of the file in a message box.

Sub Example1()
Dim varResult As Variant
ResultVar = Application.GetSaveAsFilename()
If ResultVar <> False Then
MsgBox "Filename entered: " & ResultVar
End If
End Sub

When you run the above code, you should see a Save As dialog box that will let you enter a filename. 

Dialog boz that allows entering file name

When you click Save, you should see a message box with the filename you entered along with the entire file path displayed. 

Message box that shows the entire path

Note that this code will only cause the Save As dialog box to be displayed. Pressing the Save button will not actually save the file.

Explanation of the Code

Let us understand the above code (Example1) line by line:

Line 1: This line declares a variable varResult of type Variant. The Variant data type can be used to define variables that contain any type of data.

Dim varResult As Variant

Line 2: This line displays the Save As dialog box.

ResultVar = Application.GetSaveAsFilename()

Line 3: This line first checks if the user has entered a filename and there are no errors. If so, then it takes the entire path of the file, along with the filename the user specified, and displays it in a message box.

If ResultVar <> False Then
MsgBox "Filename entered: " & ResultVar
End If

VBA Code to Display Save As Dialog Box with File Filters

This sample code lets you specify a specific file filter, so that the user is only allowed to save a file of the specified file type.

Let’s say you want to only give the user the option to save their file as a text file. Here’s the code, you will need for that:

Sub Example2()
Dim varResult As Variant
ResultVar = Application.GetSaveAsFilename(FileFilter:="Text Files (*.txt), *.txt")
If ResultVar <> False Then
ActiveWorkbook.SaveAs ResultVar
End If
End Sub

When you run this code you will see the Save As dialog box displaying just one option in the ‘Save as type’ dropdown list – ‘Text Files’, and when you subsequently save the file, you will get the file saved as a ‘.txt’ file. 

Save as Dialog with the option to choose file type

Explanation of the Code

Let us understand the above code (Example2) line by line:

Line 1: This line declares a variable varResult of type Variant.

Dim varResult As Variant

Line 2: This line displays the Save As dialog box, providing the user the option to save the file as a text file only. The string ‘Text files’ will be displayed in the drop down list for the ‘Save as type’ field and the filename specified by the user will be appended with the extension ‘.txt’.

ResultVar = Application.GetSaveAsFilename(FileFilter:="Text Files (*.txt), *.txt")

Line 3: This line first checks if the user has entered a filename and there are no errors. If so, then it saves the active workbook with the file name specified by the user as a text file.

The SaveAs() method is what actually saves the file when the ‘Save’ button is pressed.

Without this line, only the Save As dialog box would be displayed but nothing would get saved when the user presses the Save button.

If ResultVar <> False Then
ActiveWorkbook.SaveAs ResultVar
End If

You can also use the GetSaveAsFilename method to specify multiple filters.

For example, say you want to give the user the option to save their file as either a text file (*.txt) or an Excel file (*.xlsx). In that case, you can replace line 2 in the above code with the following line:

ResultVar = Application.GetSaveAsFilename(FileFilter:="Text Files (*.txt), *.txt, Excel Files (*.xlsx), *.xlsx")

Here’s what the dialog box would then look like:

Only the specified options show up in the save as dialog box

VBA Code to Display Save As Dialog box from a Specified Start Location

This last sample code lets you specify a particular location, drive or directory from where you want the Save As dialog box to start. Let’s say you want the dialog box to start from the D: drive. Here’s the code, you will need for that:

Sub Example3()
Dim varResult As Variant
ResultVar = Application.GetSaveAsFilename(InitialFileName:="D:")
If ResultVar <> False Then
MsgBox "Filename entered: " & ResultVar
End If
End Sub

Note that the InitialFileName argument just provides the location from which the Save As dialog box starts.

The user will have the option to change this location, drive or directory as needed.

Automatically picks up the save location drive

In this tutorial we demonstrated, with the help of a few sample VBScript codes, how to use the GetSaveAsFilename() method.

This method allows the user to enter the name and location of a file using the Save As dialog box. We hope you found this helpful.

Other articles you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

3 thoughts on “Using Application.GetSaveAsFilename in VBA in Excel (Examples)”

  1. “It does not, however, actually save the file even when the user presses the Save button. You will need to use a separate function if you want the file to be saved.”

    ..so why not be helpful and give an example ?

    Reply
    • Yes, it will not save the file, and this is also mentioned in the article. For saving the file, we will need to use another method, which I can cover in another article.

      Reply

Leave a Comment