Save As to automatically overwrite an existing file

[grahamiwa]

I have a Save As statment in a macro that saves a copy of the file to a different folder with the days date inserted in the file name. It works fine.

If there is already a copy of the file in that folder with the same file name I get the normal message box asking if I want to replace the file with the one I an trying to save.

Is there a way to inhibit this message box appearing and automatically overwrite any existing file with the newer version.

 

[Airfix9]

Bound the save command with

Application.DisplayAlerts = False
CODE
Application.DisplayAlerts = True

 

[grahamiwa]

My VB knowledge & Skills are not great at the moment so I rely on tons of help from this forum whie I learn

The code I currently have which works the way I want is

Dim strFileName As String

strFileName = "\\mlbdat02\shared\organization\T&D-All\ePTW Course Scheduling\ePTW Schedule Master\ePTW Schedule Master R1" & Chr(32) & Format(Date, "d mmm yyyy") & ".xls"
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
MsgBox "Saving File"

ActiveWorkbook.SaveAs Filename:=strFileName, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
ActiveWorkbook.Close

I assume that I can delete the MsgBox "Saving File" and add the additional code on the line before ActiveWorkbook.Close so it will look like this

Dim strFileName As String

strFileName = "\\mlbdat02\shared\organization\T&D-All\ePTW Course Scheduling\ePTW Schedule Master\ePTW Schedule Master R1" & Chr(32) & Format(Date, "d mmm yyyy") & ".xls"

ActiveWorkbook.SaveAs Filename:=strFileName, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False

Application.DisplayAlerts = False
CODE
Application.DisplayAlerts = True

ActiveWorkbook.Close


Did I get this right??

 

[Airfix9]

No. "CODE" was where your saveas code goes.

Dim strFileName As String

strFileName = "\\mlbdat02\shared\organization\T&D-All\ePTW Course Scheduling\ePTW Schedule Master\ePTW Schedule Master R1" & Chr(32) & Format(Date, "d mmm yyyy") & ".xls"
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=strFileName, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
Application.DisplayAlerts = True

ActiveWorkbook.Close

 

[grahamiwa]

I turned my brain on after the second try and realised the CODE meant my "SaveAs" code

It works perfectly

My thanks