Excel VBA – Save As (SaveAs) Without Confirmation

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!

Advertisements

3 Responses to “Excel VBA – Save As (SaveAs) Without Confirmation”


  1. 1 Dave November 9, 2008 at 3:28 am

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

  2. 2 movefirstblog November 10, 2008 at 11:01 am

    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).

  3. 3 JF December 2, 2014 at 8:19 pm

    I saved an excel document in SharePoint as a template for a new document. I wanted to control everything so the user can’t change the FileName nor the path, so I don’t want any prompt at all. The methods Application.DisplayAlerts = False and ThisWorkbook.Saved still prompted the user.

    The trick that did it for me is the “cancel=true”, which I used after I saved the file.
    Problem solved.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Blog Stats

  • 223,208 hits

Counter

ClustrMap


%d bloggers like this: