MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Save As and Replace

Posted by Graham on June 11, 2001 11:53 PM

I have a macro that imports data, rearranges it and then Saves As an existing xls. How do I tell my macro that it's OK to replace the existing file. Recording the keystrokes doesn't work.

Thanks in advance.


Posted by JAF on June 12, 2001 1:04 AM

before the SaveAs command, if you include the line:
Application.DisplayAlerts = False

and then after your SaveAs command, include the line:
Application.DisplayAlerts = True

This will ignore the alert that asks if you want to replace the existing file.


Posted by bj on June 12, 2001 3:40 AM

Not too sure what you mean... wouldn't a if then statement work here?

Posted by JAF on June 12, 2001 4:19 AM

Full code as follows...

Sub Save_As_Code_No_Alerts()
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="C:\TestFiles\SaveAsTest.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Application.DisplayAlerts = True
End Sub