VBA Save File Dialog, FileDialog(msoFileDialogSaveAs)

There may be times when you need to ask the user to select a location to save a file. This can be done using the save file dialog. Keep in mind that the save file dialogs doesn’t actually save anything. It only returns the full path the user has selected to save the file.

The method for creating the save file dialog explained in this article uses the command FileDialog(msoFileDialogSaveAs). It has only one problem. You can’t apply file type filters. If file type filters are essential, there are 2 things you could do:

  1. If you are using VBA for Excel, you could use the method described in the article, Excel VBA Save File Dialog, GetSaveAsFilename()
  2. If you are not using VBA for Excel you will have to use windows API, please see MSDN, Microsoft Display Open and Save As Dialog Boxes in Access with API Functions.

Jump To:

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


Contents

Example:

In this example a dialog will open asking the user to select a location to save the file. The path selected will be displayed in a message box:

Sub Example1()
Dim intChoice As Integer
Dim strPath As String

'make the file dialog visible to the user
intChoice = Application.FileDialog(msoFileDialogSaveAs).Show
'determine what choice the user made
If intChoice <> 0 Then
    'get the file path selected by the user
    strPath = _
        Application.FileDialog(msoFileDialogSaveAs).SelectedItems(1)
    'displays the result in a message box
Call MsgBox(strPath, vbInformation, "Save Path")
End If
End Sub

Result:

Excel, VBA, Save File Dialog

The following details were selected to save the file:

  • File Name: “Test”
  • File Path: “D:Temp”
  • File Type: Excel Workbook (*.xlsx)

The resulting path is displayed in a message box:

Excel VBA, Save File Dialog, Example 1 Result


Custom Title:

By default the title of the Save File Dialog is “File Save”:

Excel, VBA, Save File Dialog Default Title

Using the .Title property you can set a custom title for the dialog. In the example below the title “Random Title For Dialog” will be used:

Sub Example2()
Dim intChoice As Integer

'change the display name of the save file dialog
Application.FileDialog(msoFileDialogSaveAs).Title = _
    "Random Title For Dialog"
'make the file dialog visible to the user
intChoice = Application.FileDialog(msoFileDialogSaveAs).Show
'determine what choice the user made
If intChoice <> 0 Then
    'your code here
End If
End Sub

Result:

Excel VBA, Save File Dialog, Custom Title


Start Folder Path, InitialFileName:

Lets say there is a specific folder we would want the dialog to start in. This could be set by the InitialFileName property. In the example below the save file dialog will start in the directory “D:TempFolder to Start”:

Sub Example3()
Dim intChoice As Integer

'Select the start folder
Application.FileDialog(msoFileDialogSaveAs).InitialFileName _
    = "D:TempFolder to Start"
'make the file dialog visible to the user
intChoice = Application.FileDialog(msoFileDialogSaveAs).Show
'determine what choice the user made
If intChoice <> 0 Then
    'your code here
End If
End Sub

Result:

Excel, VBA, Save File Dialog, Start Folder


Filter File Types, Filters:

Unlike the Open File Dialog where you could change the file type filters, the save file dialog will generate an error if you try to modify the file type filters. As explained at the start of this article if file filters are essential, there are 2 options you could consider:

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

4 thoughts on “VBA Save File Dialog, FileDialog(msoFileDialogSaveAs)”

Leave a Reply

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