Declining a "Save As" produces an error

My application generates  a number of Excel spreadsheets.
My code automates naming the spreadsheet, and saving it. Code line:

.Application.Workbooks(sCurrentName).SaveAs (sSavePath & sWorkbookName)

It may happen that the destination folder already contains an older file with the same name.
If this happens, the user is asked if the old file should be replaced with this one.
If "No" is selected, the VB6 code receives an error.

I've worked around this by setting a flag just prior to the above line of code, then in the ErrorHandler, jumping over the error message box.

That works, but it is a cobble. It means that I really am cheating, because I don't know why the error is triggered.

Is there a cleaner way to handle this?
Or should I use my cobble?
Who is Participating?
The error is to give you, the designer, the opportunity to do something else. This opportunity is not available in all applications. Word, for instance will always overwrite the existing file without demur.

You can reproduce that behaviour in Excel by suppressing alerts:

Application.DisplayAlerts = False

Failing that you could test for an existing file and do something else

If Dir$(sSavePath & sWorkbookName) = "" Then
   .Application.Workbooks(sCurrentName).SaveAs (sSavePath & sWorkbookName)
   sWorkbookName = replace(sWorkbookName, ".", "_1.")
  .Application.Workbooks(sCurrentName).SaveAs (sSavePath & sWorkbookName)
End If
There is a parameter on the SaveAs operation that doesn't involve the user's approval.  Think of it as a silent save.
NormaPosyPhysicistAuthor Commented:
Thank You.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.