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
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.
A fantastic subroutine!
Easy to get working and modify to suit my individual needs.
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?
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.
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.
Search Google for the following:
“Declare Function GetSaveFileName Lib comdlg32?
(keep the quotes)
i’m using the getsavefilename and i need to change the directory (Save in:)
???
thank you.
‘ 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
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!
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.
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.
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
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
Thanks, Wes. You saved me at least an hour of googling!!
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.
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
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
Thanks Patrick…this code works just fine.
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:
” 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:
‘ 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”)
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.