One of the most important things a VBA procedure can do is save a file, especially a file that’s been substantially modified by that procedure.
This functionality is even better if the user is given the opportunity to specify a path and file name. You can simply pop up Excel’s own Save As dialog:
Application.Dialogs(xlDialogSaveAs).Show sFileNameThis approach is fine for simple applications, but it saves the file before execution returns to the calling procedure. This may not be what you want to do with the file and file name.
Excel’s VBA object model has a more flexible approach. The GetSaveAsFilename method opens a Save As dialog and gives the user control over directory and file name. GetSaveAsFilename does not save the file, but only retrieves the combined path and file name and returns it to the executing procedure for further use. If the user cancels, the function returns the string “False” instead of a filename.
Below is an implementation of GetSaveAsFilename that I’ve wrapped in a function called GSAFN. The function takes a file name and path, which are used as the defaults in the Save As dialog. If the file name entered by the user matches an existing file name, it asks the user what to do: overwrite the existing file, enter another file name, or cancel the save operation. If the user cancels the save, GSAFN returns a zero length string.
Function GSAFN(sFileName As String, sPathName As String) As String
Dim sFullName As String
Dim sPrompt As String
Dim sCurDir As String
Dim iOverwrite As Long
If ActiveWorkbook Is Nothing Then GoTo ExitSub
' save current directory, restore it later
sCurDir = CurDir
' switch to desired directory
If Len(sPathName) > 0 Then
ChDrive sPathName
ChDir sPathName
End If
' loop until unique name is entered
Do
sFullName = Application.GetSaveAsFilename(sFileName, _
"Excel Files (*.xls),*.xls", , _
"Browse to a folder and enter a file name")
If Len(sFullName) = 0 Then GoTo ExitSub
If sFullName = "False" Then GoTo ExitSub
' if name is unique, exit loop and save file
If Not FileExists(sFullName) Then Exit Do
' tell user that the filename is in use
' parse filename
sFileName = FullNameToFileName(sFullName)
sPathName = FullNameToPath(sFullName)
' construct message
sPrompt = "A file named '" & sFileName & "' already exists in '" _
& sPathName & "'"
sPrompt = sPrompt & vbNewLine & vbNewLine & _
"Do you want to overwrite the existing file?"
' ask user what to do
iOverwrite = MsgBox(sPrompt, vbYesNoCancel + vbQuestion, _
"File Exists")
Select Case iOverwrite
Case vbYes
' overwrite existing file
Exit Do
Case vbNo
' do nothing, loop again to get new filename
Case vbCancel
' bail out
GoTo ExitSub
End Select
Loop
' finally, save the file using filename from above
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs sFullName
Application.DisplayAlerts = True
GSAFN = sFullName
ExitSub:
' restore previous current directory
ChDrive sCurDir
ChDir sCurDir
End FunctionThe following procedures are used in the main procedure above. The FileExists function is more flexible than simply using Dir, because using it does not reset the initial file spec used in Dir. The FullNameToFileName and FullNameToPath functions parse the combined path and file name into a separate path and file name, making the GSAFN function work more easily. They were originally written for Excel 97, before VBA had the RevInStr string function; don’t laugh at the antiquated syntax, because they still work just fine.
Function FileExists(ByVal FileSpec As String) As Boolean
' Karl Peterson, Former MS VB6 MVP
Dim Attr As Long
' Guard against bad FileSpec by ignoring errors
' retrieving its attributes.
On Error Resume Next
Attr = GetAttr(FileSpec)
If Err.Number = 0 Then
' No error, so something was found.
' If Directory attribute set, then not a file.
FileExists = Not ((Attr And vbDirectory) = vbDirectory)
End If
End Function
Public Function FullNameToFileName(sFullName As String) As String
Dim iPathSep As Long
iPathSep = InStrRev(sFullName, Application.PathSeparator)
FullNameToFileName = Mid$(sFullName, iPathSep + 1)
End Function
Public Function FullNameToPath(sFullName As String) As String
''' does not include trailing backslash
Dim iPathSep As Long
iPathSep = InStrRev(sFullName, Application.PathSeparator)
FullNameToPath = Left$(sFullName, iPathSep - 1)
End FunctionGSAFN is called as follows:
Sub TestGSAFN()
Dim sFile As String
sFile = GSAFN("testfile.xls", "C:\Temp")
If Len(sFile) > 0 Then
MsgBox "File successfully saved"
Else
MsgBox "File was not saved"
End If
End SubGSAFN and the supporting functions are so helpful that I include them in a general Tools module which I drop into each new project I develop.
JP says
I think you meant
sFile = GSAFN(“testfile.xls”, “C:\Temp”)
?
Also, any plans to publish that Tools module?
Jon Peltier says
Jimmy –
Oops! I do the same thing for GetOpenFileName. I copied the wrong test procedure.
I have a bunch of other things in that module which I could blog about. Maybe I’ll milk it for a few more posts, then present the whole module. I’ve already shared these helper functions in File Name Functions.
JP says
Good deal. You might get some downloads if you put everything into a module and exported it as a .bas. Just a thought.
Bob Flanagan says
Jon, I think you need one more check: To see if there is already a file open by the name the user specifies:
dim wB as workbook
set wb = nothing
on error resume next
set wb = workbooks(sFileName)
on error goto 0
if not wB is nothing then
Msgbox “A file by the name you specified is already open. Please specify another name”
end if
Jon Peltier says
Thanks, Bob. Good point. Also have to make sure that we allow the file being saved to already have the selected name, so it’s even a bit more intricate. When I get a moment, I’ll tweak the code.
Jan Karel Pieterse says
Does the FileExists function work on UNC paths too?
Jon Peltier says
In a quick test on my office network, I was able to use FileExists to check for a file on another share, using \\share\path\file.ext addressing.
Bryan says
Hello, the code above seems to very long to achieve such a simple things. I have used the Application.GetSaveAs function, and was also able to specify what the title will be. However, I cannot for the life of me get it to actually save. Isn’t there some code that would save the file as the new file name and type right after the user pressed “save” from the dialog box? I placed and “application.save” in the code so when the user presses save, it saves it, but it doesn’t do what I would like. Any help?
Jon Peltier says
Bryan –
At the top of the article, I do state that if you don’t need much functionality around the save-as routine, you could use Excel’s built-in Save-As dialog.
GetSaveAsFileName is not designed to save a file, it’s designed to get the save-as file name. This allows the code to do various things before saving, and even skip the save if that’s what the programmer intended. This particular routine checks for duplicate file names, but it could instead automatically overwrite the existing file, or append duplicates with -1, -2, etc., or even append a date .
You might notics a line that says
ActiveWorkbook.SaveAs sFullName
This is the command that actually saves the workbook.
Bryan says
But, can I used the built in Save-As dialog if i want to specify with the code what the file name will be?
Jon Peltier says
Bryan –
Do you just want to save the file, or do you want to present the user with a default file name that they can change?
If you’re just saving the file, skip the dialog altogether. To insert a default file name into the Save As dialog, use this syntax:
Application.Dialogs(xlDialogSaveAs).Show arg1:=”C:\my documents\test.xls”
(Thanks to Dave Peterson, whose answer to the same question appeared in several places, including http://www.pcreview.co.uk/forums/thread-966348.php).
Bryan says
Thank you. This is going through the steps and saving my file, but when i go to re-open the file that was saved, I get a corrupt error saying that the file type is not correct. Here is my code:
Sub SelectSaveFileName()
Dim TheFile As Variant
TheFile = Application.GetSaveAsFilename(Sheets(“Test Report”).Range(“$B$4”).Value & “.xls”, _
“Workbook (*.xls), *.xls”, , “Please save you file:”) ‘ SELECTS THE CELL AS THE FILE NAME
If TheFile = False Then
MsgBox “You cancelled; Your file was NOT saved!”
Else
ActiveWorkbook.SaveAs TheFile
MsgBox “You have saved ” & CStr(TheFile)
End If
End Sub
The file I run my macro on starts as a CSV file, but I want to save as an xls file. Do you see any issues with this? The macro is on my personal.xls now, but i want to in the end deploy as an addin. This is just the save subroutine from my macro–it is much longer. Thanks!
Bryan
Jon Peltier says
Bryan –
If the file was imported as a CSV file, you have to tell Excel what format to save it in:
ActiveWorkbook.SaveAs TheFile, xlWorkbookNormal
Bryan says
Thank you. This worked great.
darro says
Hi,
How would I use this code to open the save as dialogue so the user can choose the name and save location, but when the file is saved have formulas converted to values?
Jon Peltier says
Darro –
Before this line
you make any necessary changes in the workbook. This includes in your case, copying each worksheet’s contents, and using Paste Special – Value.
Jay says
I had a question about saving files with the FIleFormat specified. I ran across this while trying to use your code to save as a xlsm file. I finally figured out that I needed to add FileFormat:=xlOpenXMLWorkbookMacroEnabled to the end of you line that reads ActiveWorkbook.SaveAs sFullName. However, this will save all files in this format, correct? How would you alter the code to possibly send the format in as a parameter with xlsx as the default? This is an awesome piece of code and thanks for any help.
Jon Peltier says
Jay –
That problem led to a day or so of unbillable time last year, because it took that long to figure out the intricacies of Save and SaveAs in programs which may be used in Exce l2003 and 2007, what with compatibility mode, macro enabled files, and so forth. Just reading your comment brings back the headache I experienced.
You would have to change the filter in GetSaveAsFilename from “Excel Files (*.xls),*.xls” to something more flexible.
In 2007:
“Excel Workbook (*.xlsx),*.xlsx, Excel Macro-Enabled Workbook (*.xlsm),*.xlsm, Excel 97-2003 Workbook (*.xls),*.xls”
In 2003:
“Excel Workbook (*.xls),*.xls, Excel 2007 Workbook (*.xlsx),*.xlsx, Excel 2007 Macro-Enabled Workbook (*.xlsm),*.xlsm”
These will show all of the file types listed in the save as type dropdown, and if you don’t specify a FilterIndex, it will select the first type by default.
Then when saving, you will need to use the appropriate xlFileFormat parameter:
In Excel 2003:
xlWorkbookNormal for .xls
51 for .xlsx
52 for .xlsm
(only numerical values, not named constants)
In 2007:
xlOpenXMLWorkbook for .xlsx
xlOpenXMLWorkbookMacroEnabled for .xlsm
xlExcel8 for .xls
And there is some compatibility mode checking that you need to do if you want to save as xlExcel8 in 2007.
Sam says
Hi Jon Peltier, how about saving the file as .csv;
I am using this
objXlBook.SaveAs csvFileName, XlFileFormat.xlCurrentPlatformText
but the problem here is some data (number) are not getting correctly saved.
Like
59829220084018 -> 5.98292E+13
24143490084016 -> 2.41435E+13
Any advice with will be highly appreciated,
Best Regards,
Sam
Jon Peltier says
format the cells in Excel as Number with no decimals, and make sure the column is wide enough to display the entire number. Now exporting as CSV will preserve all digits.
Dr. Schwartz says
Hi Jon, I use Excel 2010 and am also a fan of the GetSaveAsFileName for the same reason as you describe in your post:
“The GetSaveAsFilename method opens a Save As dialog and gives the user control over directory and file name. GetSaveAsFilename does not save the file, but only retrieves the combined path and file name and returns it to the executing procedure for further use.”
But can we be sure that a temporary file is not created in the process? The problem is that in my application the user must browse and name a file in a folder where the user has restricted access (rights to read and add files). What happens is that the file gets created (although empty) and I get and error saying “You don’t have permission to modify files in this network location.” I have tested it in a folder where the user has write permission where it works perfectly. Are you familiar with this feature?
If we assume that a temp file is created, then I need a different solution. Do you know of a another simple way to input file name and path?
Any thoughts would be highly appreciated
Jon Peltier says
Hi Doc –
Temp files are created when an existing file is opened, I think to collect changes since the last save. During saves another temp file is created where the changes are merged with the original file; the original file is then deleted and the temp file is renamed to match the original name.
I don’t know if a temp file is created when a file is saved for the first time. If one is, it should be saved in the target directory or in the temp directory. I just tried a quick test, but if a temp file is saved to either of these locations, it happens facter than Windows can be bothered to display.
But in any case, whether a temp file is created or not, the user won’t be able to save the permanent file to a directory where he has no permission to do so.
Dr. Schwartz says
Hi Jon, thanks for your thoughts. Just to elaborate on the folders permission settings. It is set up so the user can create new files but is not allowed to modify or delete (regulatory requirement to ensure data integrity). So in this case if Excel (GetSaveAsFilename) creates a temporary file, which it appears to, then I get the error message as Excel tries to remove it again.
I found a workaround, although had to compromise as it is a two-step procedure. I use a simple Inputbox to retrieve the filename and the FileDialog(msoFileDialogFolderPicker) to get the path from the user. Not pretty, but it works.
Thanks for your time / Doc
Jon Peltier says
So the error occurs during the use of GetSaveAsFileName, not during the actual save? That’s even more strange.
Adeniji Segun says
Could you please explain to me how to make just a copy of the Activeworkbook and write it as a New file with the getsaveas. The Idea is that I do not want to overwrite my active workbook but make a copy of it with a different name to the copied version and I also want the copied version closed but placed in the Folder.
Thanks for your anticipated help
Jon Peltier says
Adeniji –
Try this:
ActiveWorkbook.SaveCopyAs sFullName