SaveAs command when a file already exists?
Hi Everybody,
I have a "CommandButton" on a Worksheet which implements the the "Save" and "SaveAs" features normally found in the "File" menu. The click event implements logic which decides whether to execute the "ActiveWorkbook.Save" or the "ActiveWorkbook.SaveAs (FileName)" command. My problem is when the .SaveAs command is executed and an existing file exists with the same file name is in the target directory, the user gets a message box asking whether the existing file should be overwritten. This message box is a feature of Excel and Windows itself i.e. I'm not implementing custom code for this feature to happen. If the user selects the "Cancel" button I need to make sure my code for "ActiveWorkbook.SaveAs (FileName)" isn't executed. Is there a way to pick-up whether the user selects "Cancel"? Thanks for any help! Sam P.S. I'm using Excel 97. |
SaveAs command when a file already exists?
do you want the user to make a decision?
if you want to overwrite the file without prompt Application.DisplayAlerts = False activeworkbook.SaveAs Filename Application.DisplayAlerts = True check and prompt in your code filename = Application.GetSaveAs() if dir(filename) < "" then res = msgbox "delete " & filename & "?" if res = vbYes then Kill filename end if ActiveWorkbook.Saveas Filename since the way things are now, you are not putting up the dialog, there is no way to capture the users response, so to take control of the situation, you need to avoid having excel put up the dialog by removing the need for it yourself. -- Regards, Tom Ogilvy "debartsa" wrote in message ... Hi Everybody, I have a "CommandButton" on a Worksheet which implements the the "Save" and "SaveAs" features normally found in the "File" menu. The click event implements logic which decides whether to execute the "ActiveWorkbook.Save" or the "ActiveWorkbook.SaveAs (FileName)" command. My problem is when the .SaveAs command is executed and an existing file exists with the same file name is in the target directory, the user gets a message box asking whether the existing file should be overwritten. This message box is a feature of Excel and Windows itself i.e. I'm not implementing custom code for this feature to happen. If the user selects the "Cancel" button I need to make sure my code for "ActiveWorkbook.SaveAs (FileName)" isn't executed. Is there a way to pick-up whether the user selects "Cancel"? Thanks for any help! Sam P.S. I'm using Excel 97. |
SaveAs command when a file already exists?
Thanks Tom!
"Tom Ogilvy" wrote in message ... do you want the user to make a decision? if you want to overwrite the file without prompt Application.DisplayAlerts = False activeworkbook.SaveAs Filename Application.DisplayAlerts = True check and prompt in your code filename = Application.GetSaveAs() if dir(filename) < "" then res = msgbox "delete " & filename & "?" if res = vbYes then Kill filename end if ActiveWorkbook.Saveas Filename since the way things are now, you are not putting up the dialog, there is no way to capture the users response, so to take control of the situation, you need to avoid having excel put up the dialog by removing the need for it yourself. -- Regards, Tom Ogilvy "debartsa" wrote in message ... Hi Everybody, I have a "CommandButton" on a Worksheet which implements the the "Save" and "SaveAs" features normally found in the "File" menu. The click event implements logic which decides whether to execute the "ActiveWorkbook.Save" or the "ActiveWorkbook.SaveAs (FileName)" command. My problem is when the .SaveAs command is executed and an existing file exists with the same file name is in the target directory, the user gets a message box asking whether the existing file should be overwritten. This message box is a feature of Excel and Windows itself i.e. I'm not implementing custom code for this feature to happen. If the user selects the "Cancel" button I need to make sure my code for "ActiveWorkbook.SaveAs (FileName)" isn't executed. Is there a way to pick-up whether the user selects "Cancel"? Thanks for any help! Sam P.S. I'm using Excel 97. |
All times are GMT +1. The time now is 02:17 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com