Subscribe for Weekly Tutorials
BONUS: subscribe now to download our Top Tutorials Ebook!
Excel VBA SaveAs to Overwrite an Existing File Without Prompt
How to allow your macro/vba code to overwrite an existing Excel file.
This allows you to do things like, export a weekly report to a specific file and have it overwrite that file each week.
The technique in this tutorial does not require any confirmation in order to overwrite the file.
Sections:
Overwrite A File Using VBA
Add these two lines to any macro to allow them to overwrite a file without having the confirmation window appear:
Add to the top of the macro:
Application.DisplayAlerts = False
Add to the bottom of the macro:
Application.DisplayAlerts = True
Application.DisplayAlerts controls if Excel will show pop-up window alert messages, such as when you go to overwrite an existing file and Excel wants to warn you about that.
When you set this value to False, these messages will not appear and, so, you won't have to confirm anything that the macro does.
Make sure to set it back to True at the end of the macro so that Excel will function normally again after the macro has finished running.
Example
Here is a simple macro that you can use to test this out:
Sub Save_File_Overwrite()
' Save the current workbook as Test.xlsm
' 51 for regular file
' 52 for macro enabled workbook
ThisWorkbook.SaveAs "C:\Test.xlsm", 52
End Sub
Run the above macro twice from a macro-enabled workbook. The second time that you run it, you will see a confirmation dialogue box asking if you want to overwrite the existing file or not.
Add the DisplayAlerts lines of code to the file and try it again:
Sub Save_File_Overwrite()
' Don't show confirmation window
Application.DisplayAlerts = False
' Save the current workbook as Test.xlsm
' 51 for regular file
' 52 for macro enabled workbook
ThisWorkbook.SaveAs "C:\Directory\Test.xlsm", 52
' Allow confirmation windows to appear as normal
Application.DisplayAlerts = True
End Sub
Now, the file will overwrite the existing file without making a mention of it.
Notes
Be careful! When you disable alerts in Excel, data can be overwritten without you knowing about it. If you have a large and complex macro that works fine except for one area where you want to save the file, disable alerts only for that one area and enable them afterwards for the rest of the macro; this reduces the chance that other data will get overwritten without warning.
Download the sample file if you want to see the above example in Excel.
Question? Ask it in our Excel Forum
Tutorial: Create a pop-up message box in Excel using VBA Macros. This allows you to show a message t...
Tutorial: Logical operators in VBA allow you to make decisions when certain conditions are met. They...
Tutorial: Ill show you three different ways to create an array in Excel VBA and Macros and how to ge...
Tutorial: I'll show you how to loop through an array in VBA and macros in Excel. This is a fairly...
Tutorial: How to use VBA/Macros to iterate through each cell in a range, either a row, a column, or ...
Tutorial: I'll show you how to create a message box popup window in Excel that contains text on mult...