SaveAs command when a file already exists?

[debartsa]

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.

 

[Tom Ogilvy]

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.

 

[debartsa]

Thanks Tom!

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.