Failure at File Save

[Paige]

I am doing a file save as, per the code below.

Dim fName as Variant
Do
fName = Application.GetSaveAsFilename(InitialFileName:="",
FileFilter:="Microsoft Excel Workbook (*.xls), *.xls")
Loop Until fName <> False
ActiveWorkbook.SaveAs Filename:=fName, FileFormat:=xlWorkbookNormal

It works fine if the user types in a new file name and saves, or if the user
selects an existing file and elects to replace the existing file with the new
one. However, it doesn't work in the following scenario:

User selects an existing filename and hits 'Save'; a dialog box comes up
that says "File name already exists...do you want to replace it?", with the
options of 'Yes', 'No', and 'Cancel'. If the user selects either 'Cancel' or
'No' on the dialog (versus 'Yes'), it gives a run-time 1004 error: Method
'Save as' of '_Workbook' failed and goes to the last line of my code above.
I can't figure out how to capture these two scenarios ('Cancel' or 'No'). If
they select 'Cancel' or 'No', it just needs to go back to the saveas dialog
box.

Can someone assist me with this please?

 

[Tom Ogilvy]

Dim fName as Variant
again:
Do
fName = Application.GetSaveAsFilename(InitialFileName:="",
FileFilter:="Microsoft Excel Workbook (*.xls), *.xls")
Loop Until fName <> False
On error Resume next
ActiveWorkbook.SaveAs Filename:=fName, FileFormat:=xlWorkbookNormal
On Error goto 0
if ActiveWorkbook.FullName <> fName then goto again

 

[Paige]

Thanks, Tom, works like a charm. One of those ingenious things that you know
how to do; am sure they don't seem ingenious to you, but to people like
me......