In Macro, saving files as...when file already exists
Posted by Vicki on July 20, 2001 2:09 PM
I want to automate a process of opening & saving multiple files, through a macro. Works great - but I can't seem to do the macro where it automatically overwrites the existing file with the new file. I know the string is:
Active workbook.close Savechanges:true
But can't seem to put it in the right place of the macro that is already written.
Thanks!
Posted by Barrie Davidson on July 20, 2001 2:24 PM
Hi Vicki, I don't understand your question. Are you saying the string you have posted is not working for you (because it shouldn't be working, the correct syntax is, ActiveWorkbook.Close Savechanges:=True, note that I added an equal sign)?
Barrie
Posted by Vicki on July 20, 2001 2:56 PM
Barrie - thanks for your reply. I changed the : to = but still shows red, so an error. Here is what the file has:
ActiveWorkbook.SaveAs FileName:= _
"O:\cp50601b\SHARED\CLUBS\P&LS\2001 P & L\2nd Qtr\1AAADCBOSv.xls", SaveChanges=true, FileFormat _
:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
False, CreateBackup:=False
Posted by Barrie Davidson on July 21, 2001 7:16 AM
Vicki, the reason it is showing red is because SaveChanges is not a valid argument for the command. Are you trying to use this argument so you don't get a message box prompting the user if they want to save changes? If yes, try using:
Application.DisplayAlerts = False
just before your save command and put:
Application.DisplayAlerts = True
after your save command.
The DisplayAlerts command instructs Excel not to use the alert message boxes.
Regards,
Barrie
Posted by Vicki on July 23, 2001 8:59 AM
Thank you so much Barrie. This was perfect & macro now works great. I plan to do this with about 200 different files in several macros, for different purposes.
Appreciate your quick help!
Vicki
