When saving a file in Excel from VBA code it is sometime useful to avoid the display of a Save As dialog if the file to be saved already exists (i.e. to overwrite any exisiting files).
This is pretty easy but one of those things I always forget how to do!
Here’s the code:
Application.DisplayAlerts = False ActiveWorkbook.SaveAs (etc.) Application.DisplayAlerts = True
Thanks to the users at http://www.vbforums.com/showthread.php?t=528826 who reminded me of this for about the hundredth time!
So I am using this code from a “button” within my Excel Spreadsheet:
Sub Save_Name_New_Application()
‘
‘ Save_Name_New_Application Macro
‘ Macro recorded 11/8/2008 by David L. Rosengarden
‘
‘
If Right(ThisWorkbook.Path, 19) “Client_Applications” Then
Dim FName As String
Do Until Right(FName, 19) = “Client_Applications”
FName = BrowseFolder(Caption:=”You Must Select the Clients_Application Folder”)
Loop
ChDir (FName)
End If
Dim File_To_SaveAs_Name As String
File_To_SaveAs_Name = Range(“D10″).Value + “loan mod app.xls”
On Error Resume Next
ActiveWorkbook.SaveAs Filename:=File_To_SaveAs_Name, FileFormat:=xlNormal, Password:=”", WriteResPassword:=”", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub
Hey – when it’s naming the file for the first time – no problem – I get a save with a unique file name.
HOWEVER – when I go to click my save button again AFTER I made changes to the spreadsheet – it goes through the Save As routine – asks me if I want to overwrite (which I want in this case) I see the progress bar at the bottom left do its thing for a save. But when I close out of the spreadsheet and reopen the file that was just saved – the fields I changed are back to what they were BEFORE I pressed my save routine!!!!
WHAT GIVES???
Dave – interesting!
When I run your code on my machine it works just fine. (i.e. it asks for confirmation to save, saves and when the file is closed and re-opened it has saved all the changes).