MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Auto Replace File


Posted by Thomas Venn on April 12, 2000 10:50 AM

Hi,

I have the following code:

Sub Macro1()
Workbooks.Add
ChDir "H:\15 and 17"
ActiveWorkbook.SaveAs Filename:="H:\15 and 17\Book001.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
ActiveWindow.Close
ReplaceFile = True
Workbooks.Add
ActiveWorkbook.SaveAs Filename:="H:\15 and 17\Book002.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
ActiveWindow.Close
End Sub


Now, when I run the macro, it gives me the message, "A file named "H:\15 and 17\Book001.xls" already exists in this location. Do you want to replace it?".

I want to replace the file everytime. I tried adding " Application.DisplayAlerts = False" to the macro, but that does not work. Please help if you can.


Thanks in advance,

Thomas


Posted by Celia on April 12, 2000 4:39 PM


Thomas
" Application.DisplayAlerts = False" should work.
Try the following (also I have deleted some of the unnecessary code) :-

Sub Macro1()
Workbooks.Add
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="H:\15 and 17\Book001.xls"
ActiveWindow.Close
Workbooks.Add
ActiveWorkbook.SaveAs Filename:="H:\15 and 17\Documents\Book002.xls"
ActiveWindow.Close
Application.DisplayAlerts = True
End Sub

Celia