• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 309
  • Last Modified:

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?
2 Solutions
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.
NormaPosyAuthor Commented:
Thank You.

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now