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)
