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
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!!