MrExcel Publishing
Your One Stop for Excel Tips & Solutions

VBA Overwrite Existing File


Posted by Richard S on July 05, 2001 1:03 AM

I have recorded a macro which opens a blank file, copies data and format from another file, then saves it as a specific name. It then emails the workbook, closes it, then repeats for the next workbook. The only problem is, when he Macro gets to the command "ActiveWorkbook.SaveAs" command, I get a prompt that the filename already exists, and do I want to replace it. I have no need to keep the file, so I answer yes. Is there a command which will do this for me, as the Macro repeats about 150 times, and I don't want to sit there clicking yes all the time.
Any help appreciated
Richard


Posted by Ivan F Moala on July 05, 2001 1:36 AM

Richard
If you search this list you will find it has
been answered a number of times....perhaps as
a different Q but invariably the same technique
is used = application.displayalerts
Lookup Online help for this........

Sub yourroutine()

Application.DisplayAlerts = False

your other code here....

ActiveWorkbook.SaveAs "test123"

other code here....

'Reset it back to True before exiting!
Application.DisplayAlerts = True

End sub

HTH

Ivan

Posted by Russell on July 05, 2001 8:52 AM

Another way....

You can also do this:

Kill strFileName
ActiveWorkbook.SaveAs strFileName

You may need to put some error handling in if you do it this way if strFileName may not exist.

HTH,

Russell

Posted by Richard S on July 05, 2001 4:39 PM

Thanks Guys (NT)