GetSaveAsFilename

Similar to GetOpenFilename, the Application object has another method called GetSaveAsFilename. This one displays the standard Save dialog box. Just like with GetOpenFilename, you can test whether the result equals False to determine if the user clicked the Cancel button. Other similarities are:

  • The FileFilter argument works the same
  • The FilterIndex argument works the same
  • The ButtonText argument works the same if you use a Macintosh
  • The Title argument works the same
  • The method returns a string file name, it doesn’t actually do any saving

Unlike GetOpenFilename, there’s no MultiSelect argument because that would just be silly. One nice feature is the InitialFilename argument which lets you suggest a filename – prefilled in the “File name” box.

Here’s an example that prompts the user for a filename, then creates a tab delimited file from the contents of Sheet1.

Sub SaveTextFile()
    Dim sFname As String
    Dim lFnum As Long
    Dim rRow As Range
    Dim rCell As Range
    Dim sOutput As String
   
    sFname = Application.GetSaveAsFilename( _
        InitialFileName:="MyTabDelim.txt", _
        FileFilter:="Text files, *.txt", _
        Title:="Save Tab Delimited File")
       
    If sFname <> "False" Then
        lFnum = FreeFile
       
        Open sFname For Output As lFnum
       
        For Each rRow In Sheet1.UsedRange.Rows
            For Each rCell In rRow.Cells
                sOutput = sOutput & rCell.Text & vbTab
            Next rCell
           
            Print #lFnum, sOutput
           
            sOutput = ""
        Next rRow
       
        Close lFnum
    End If

End Sub

19 Comments

  1. Mike Kramer says:

    Dear Dick,
    Thank you very much for your sample:
    ‘GetSaveAsFilename’. It started to work
    immediately, without any changes. I couldn’t
    find similar as yours. I also like EXCEL,
    but I’m a novice to it.
    Thank you again and all the best.
    Mike.

  2. Linton says:

    A fantastic subroutine!
    Easy to get working and modify to suit my individual needs.

  3. Ben Morton says:

    This is very good functionality. I would like to be able to use it in Access. I have a module and have added the Excel 9.0 library, but still am not able to use this method. I can see it in the library with I press F2 and search for it, but when I enter it into code, no intellisence and I get and I get a compiler error – syntax error. Can it be used in an Access module?

  4. Ben: You need to create an Excel Application object

    Dim xlApp as Excel.Application
    Dim sFname as String

    Set xlApp = New Excel.Application

    sFname = xlApp.GetSaveAsFilename(etc..)

    I’ve heard there are still problems using this method, so you might want to do a google search and see what others have experienced.

  5. Jon Peltier says:

    Launching an Excel instance just to use GetSaveAsFilename seems pretty costly. I’ve used (I think) the Windows common controls to get this kind of dialog in a non-Excel application. I must have gotten the information I needed from Google, but if anyone wants, I could try to remember which project it was, and hunt down the code.

  6. Search Google for the following:

    “Declare Function GetSaveFileName Lib comdlg32?
    (keep the quotes)

  7. mjs says:

    i’m using the getsavefilename and i need to change the directory (Save in:)

    ???

    thank you.

  8. Jon Peltier says:

    ‘ save the current directory
    sCurDir = CurDir

    ‘ change drive path to desired path (sPath)
    ChDrive sPath
    ChDir sPath

    ‘ do your GetSaveAsFileName or GetOpenFilename here

    ‘ change path and directory back to what it was
    ChDrive sCurDir
    ChDir sCurDir

  9. Ben says:

    This example doesn’t work for me and I’m not sure why. I’m using VB 11.2 that came with Excel 2004 for mac (version 11.3.3). This works:

    DestFile = Application.GetSaveAsFilename()

    It opens up the save as Dialogue and I can pick a file name and destination and that is copied to DestFile just like I want. But, I’d like to only allow for saving as a text file. When I try to use this:

    DestFile = Application.GetSaveAsFilename( _InitialFileName:=”MyTabDelim.txt”, _
            FileFilter:=”Text files, *.txt”, _
            Title:=”Save Tab Delimited File”)

    I get an error saying “Compile error: Invalid character” and the first underscore “-” is highlighted. Other times I get a more generic syntax error. The macro cannot be run.

    Any ideas why this is happening to me? It seems that every reference online uses the above syntax but it’s not working for me. Any help would be appreciated!

  10. Jon Peltier says:

    Space + Underscore means this line continued on the next line. Somehow that line didn’t wrap in the source you copied. Take out the offending underscore character, or put thee cursor right after it and press Enter.

  11. Eric says:

    Only tangentially related, but it’s extremely annoying that Excel saves both CSV and Tab-delimited value files in a way which Access cannot import by default. That is, Excel throws quotes in to screw up Access whenever there’s a space in a value (even for tab-delimited files, where it makes no sense whatsoever to quote values with spaces in them). Leave it to Microsoft…

    I ended up having to write my own routine to write a proper tab-delimited file that Access can import without problems.

  12. Ben says:

    Hi John,

    Thanks for the advice, but it’s still not working for me. I think (hope) I’m just doing something dumb. Per your advice I’ve removed the underscores and made the entire thing one long line of code:

    DestFile = Application.GetSaveAsFilename( InitialFileName:=”MyTabDelim.txt”, FileFilter:=”Text files, *.txt”, Title:=”Save Tab Delimited File”)

    But I get a compile error that says “Expected: list separator or )” and the word “files” of ‘FileFilter:=”Text files,’ is highlighted.

    This also happens if I leave the underscores in place and hit “enter” after each one:

    DestFile = Application.GetSaveAsFilename( _
    InitialFileName:=”MyTabDelim.txt”, _
    FileFilter:=”Text files, *.txt”, _
    Title:=”Save Tab Delimited File”)

    Again, any help is greatly appreciated! Thanks for the help so far!

    -Ben

  13. Wes Groleau says:

    FileFilter does not work on Mac–leve it out.

    And on Windows, it does not work the way the help claims.

    The following works on Windows, but it took me at least an hour of googling and experimenting:

    Out_File = Application.GetSaveAsFilename _
    (InitialFileName:=”EDIT_THIS”, _
    Title:=”Select output file name (Cancel means NONE)”, _
    FileFilter:=”GEDCOM (*.ged), *.ged, Text (*.txt), *.txt, Any (*.*), *.*”)

    For Mac, omit the FileFilter param. If you want both, try one of these:

    If Left(Application.OperatingSystem, 3) = “Mac” Then
    If Application.OperatingSystem Like “Mac*” Then

  14. Jonathon says:

    Thanks, Wes. You saved me at least an hour of googling!!

  15. jeff weir says:

    I was hoping to use the GetSaveAsFilename method so that I could get a macro to run AFTER the save that refreshes a pivotcache connection in the case that a workbook has been renamed. (I need to do this because the pivot is querying information within the workbook using SQL. If I don’t update the connection after file rename, the sql query will be accessing the data in the previous version of the workbook, not the current version).

    Only problem is, the GetSaveAsFilename method doesn’t return whatever filetype the user picks from the FileFilter list (although I’ve only got one type listed in the below sub). So the user can’t change the format…It saves in whatever format the excel workbook is curretly in(unless no format is specified…in which case my sub saves it in .xls format).

    I’ve got two questions for the floor:
    1. Anyone got any good suggestion as to how I could allow users to change the filetype based on what they select with the GetSaveAsFilename dialogue? I’m guessing this cant be done with this method, and I’d instead have to create a dialogue box that returns both the filename and the file type. If so, can’t say i really see the point of the GetSaveAsFilename method.
    2. If I just stick with current code, can it be written any smarter? I’m very new to VBA, and keen to learn if I’m doing anything unneccessary.

    Thanks for any suggestions.

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    Dim FName As String

    If Not SaveAsUI Then Exit Sub
    Application.EnableEvents = False
    FName = Application.GetSaveAsFilename(, FileFilter:=”Excel Workbook, *.xl*”)

    If FName <> “False” Then
        If InStr(1, Right(FName, 4), “xls”) = 0 Then FName = FName & “.xls”
        ThisWorkbook.SaveAs Filename:=FName, FileFormat:=56
        End If
    Application.EnableEvents = True
    Cancel = True

    Call Update_Pivot_Connection

    End Sub

  16. See:
    http://www.rondebruin.nl/saveas.htm

    ‘In Excel 2007, SaveAs requires you to provide both the FileFormat parameter and the correct file extension.
    ‘ pass three params, first two changed by ref
    Sub GetSaveAsFileFormat(ByRef sFileName As String, ByRef lFileFormatValue As Long, ByVal sTitle As String)
    If Val(Application.Version) < 12 Then
    lFileFormatValue = -4143
    ‘Only choice in the “Save as type” dropdown is Excel files(xls)
    ‘because the Excel version is 2000-2003
    sFileName = Application.GetSaveAsFilename(InitialFileName:=sFileName, _
    FileFilter:=”Excel Files (*.xls), *.xls”, _
    Title:=sTitle)
    Else
    sFileName = Application.GetSaveAsFilename(InitialFileName:=sFileName, FileFilter:= _
    ” Excel Macro Free Workbook (*.xlsx), *.xlsx,” & _
    ” Excel Macro Enabled Workbook (*.xlsm), *.xlsm,” & _
    ” Excel 2000-2003 Workbook (*.xls), *.xls,” & _
    ” Excel Binary Workbook (*.xlsb), *.xlsb”, _
    FilterIndex:=1, Title:=sTitle)
    End If
    If StrComp(sFileName, “False”, vbTextCompare) 0 Then
    ‘ get the file format the user asked for from the file name
    lFileFormatValue = FileFormatValue(sFileName)
    End If
    End Sub

    Function FileFormatValue(sFileName As String) As Long
    If Val(Application.Version) < 12 Then
    FileFormatValue = -4143 ‘ ” Excel 2000-2003 Workbook (*.xls), *.xls,”
    ‘Only choice in the “Save as type” dropdown is Excel files(xls)
    Else
    ‘Find the correct FileFormat that match the choice in the “Save as type” list
    Select Case FileExtension(sFileName)
    Case “xls”: FileFormatValue = 56 ‘xlExcel8 ” Excel 2000-2003 Workbook (*.xls), *.xls,”
    Case “xlsb”: FileFormatValue = 50 ‘xlExcel12 ” Excel Binary Workbook (*.xlsb), *.xlsb”,
    Case “xlsx”: FileFormatValue = 51 ‘xlOpenXMLWorkbook ” Excel Macro Free Workbook (*.xlsx), *.xlsx,”
    Case “xlsm”: FileFormatValue = 52 ‘xlOpenXMLWorkbookMacroEnabled ” Excel Macro Enabled Workbook (*.xlsm), *.xlsm,”
    Case “xltm”: FileFormatValue = 53 ‘xlOpenXMLTemplateMacroEnabled Open XML Template Macro Enabled
    Case “xltx”: FileFormatValue = 54 ‘xlOpenXMLTemplate Open XML Template
    Case “xlam”: FileFormatValue = 55 ‘xlOpenXMLAddIn Open XML Add-In
    Case Else: FileFormatValue = 0 ‘ undefined, let the caller figure it out
    ‘ 46 is XML spreadsheet
    ‘ 60 is xlOpenDocumentSpreadsheet, .ODS?
    End Select
    End If
    End Function

    ‘ return the default file extension for the given workbook
    ‘ which may be different from the actual extension which may be none if unsaved
    Function DefaultFileExtension(wb As Workbook) As String
    If Val(Application.Version) < 12 Then
    DefaultFileExtension = “xls” ‘ FileFormatValue = -4143
    ElseIf wb Is Nothing Then
    DefaultFileExtension = “xlsx” ‘ Excel 2007
    Else
    Select Case wb.FileFormat
    Case 50: DefaultFileExtension = “xlsb”
    Case 51: DefaultFileExtension = “xlsx”
    Case 52: DefaultFileExtension = “xlsm”
    Case 56: DefaultFileExtension = “xls”
    Case Else: DefaultFileExtension = “xlsx” ‘ Excel 2007
    End Select
    End If
    End Function

  17. Jeff Weir says:

    Thanks Patrick…this code works just fine.

  18. jeff weir says:

    Interesting…I amended this code so that instead of the filename being blank, it returns the ActiveWorkbook.Name. However, it turns out that if the filterindex setting does not exactly match the filetype as the workbook currently has, then the ActiveWorkbook.Name will be enclosed in quotation marks, and if a user then selects another filetype from the ‘Save as type’ dropdown this has no effect unless/until they have removed those quotation marks…that is, the file is saved in the existing format no matter what the user selects.

    For instance, if your code sets out the following:

    fname = Application.GetSaveAsFilename(InitialFileName:= activeWorkbook.Name, filefilter:= _
            ” Excel Macro Free Workbook (*.xlsx), *.xlsx,” & _
            ” Excel Macro Enabled Workbook (*.xlsm), *.xlsm,” & _
            ” Excel 2000-2003 Workbook (*.xls), *.xls,” & _
            ” Excel Binary Workbook (*.xlsb), *.xlsb”, _
            FilterIndex:=2, Title:=“Save as”)

    …and your file is currently in .xlsm format with the name of test then you will get a filename of test. But if your file is currently in .xls format then you will get a filename of “test.xls” (including the quotation marks and file extension) and even if you select the “Excel macro enabled workbook” option from the “Save as type” dropdown it will still be saved as an .xls.

    To get around this, I amended the above to this:

    ‘        Need to get excel to pre-set the filterindex setting to match the existing file, rather than have a hard-coded value.
    ‘        For some reason if the current extension doesn’t match the default filterindex setting then
    ‘        the activeworkbook.name is returned in quotation marks, and whatever the user selects from the filterindex
    ‘        dropdown has no effect unless those quotation marks are removed.
           Select Case LCase(Right(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) – InStrRev(ActiveWorkbook.Name, “.”, , 1)))
            Case “xlsx”: FilterIndexValue = 1
            Case “xlsm”: FilterIndexValue = 2
            Case “xls”: FilterIndexValue = 3
            Case “xlsb”: FilterIndexValue = 4

            Case Else: FilterIndexValue = 3 ‘make this the default in the event that we get no match.
           End Select
     
            ‘Give the user the choice to save in 2000-2003 format or in one of the
           ‘new formats. Use the “Save as type” dropdown to make a choice,Default =
           ‘Excel Macro Enabled Workbook. You can add or remove formats to/from the list
           
            fname = Application.GetSaveAsFilename(InitialFileName:=ActiveWorkbook.Name, filefilter:= _
            ” Excel Macro Free Workbook (*.xlsx), *.xlsx,” & _
            ” Excel Macro Enabled Workbook (*.xlsm), *.xlsm,” & _
            ” Excel 2000-2003 Workbook (*.xls), *.xls,” & _
            ” Excel Binary Workbook (*.xlsb), *.xlsb”, _
            FilterIndex:=FilterIndexValue, Title:=“This example copies the ActiveSheet to a new workbook”)

  19. Aaron Reese says:

    you get the same issue in Excel 2003 if the filename provided contains fullstops and the last part after the last stop does not match the filters. If the filename does not contain any dots (i.e.) has no file extension in the name then the filtered file extension is used as the file type and the name displays correctly.

Posting code or formulas in your comment? Use <code> tags!

  • <code lang="vb">Block of code goes here</code>
  • <code lang="vb" inline="true">Inline code goes here</code>
  • <code>Formula goes here</code>

Leave a Reply

Here's how to update your reports of company and nearly any web data: