Method 'SaveAs' of object '_Workbook' failed

[yky]

I have this intermittent problem of getting the error message that says "Method 'SaveAs' of object '_Workbook' failed". I'd say at least 80 percent of the time I have no problem. And if there is a problem, I can solve it by deleting the existing file (thus, no overwriting). Sometimes, but not always, I don't even need to delete the existing file. I just re-run the VB script again and it would work. Once when I had the problem, I went to debug and checked every variable in the saveas method and they were fine. What could be causing the problem?

ActiveWorkbook.SaveAs Filename:=destinationPath & (MonthName(Month(voucherDate)) & _
Str(Year(voucherDate))) & "\" & strVoucherName & Format(voucherDate, "dd/mm/yyyy"), _
FileFormat:=xlWorkbookDefault

 

[xenou]

write code at the *start* of your program (or at the earliest possible point) to check if the file exists. You want to handle this prophylactically if it's something that can happen - do something about it such as delete or move/rename the earlier version (I guess in principle SaveAs would just overwrite an existing file but in that case you might also have to deal with alerts from Excel. I really don't overwrite excel files very much - keeping old files around for a while never hurts, whereas overwriting files sometimes leads to disaster).

This seems to sometimes work better for obscure reasons (creating the string first in a variable):

Dim SavePath as String
SavePath = destinationPath & (MonthName(Month(voucherDate)) & _
Str(Year(voucherDate))) & "\" & strVoucherName & Format(voucherDate, "dd/mm/yyyy")
ActiveWorkbook.SaveAs SavePath

It's getting tricky with so many file formats in use for Excel these days. I'd leave out the file format for now and see how it goes - it should save in the file format that it already is in, and that should be fine I would think.

Break your program up into discrete parts. Modularize. Don't do too much in one sub.

 

[yky]

write code at the *start* of your program (or at the earliest possible point) to check if the file exists. You want to handle this prophylactically if it's something that can happen - do something about it such as delete or move/rename the earlier version (I guess in principle SaveAs would just overwrite an existing file but in that case you might also have to deal with alerts from Excel. I really don't overwrite excel files very much - keeping old files around for a while never hurts, whereas overwriting files sometimes leads to disaster).

Thank you for the prompt reply.

Yes. I don't want to overwrite files either. But in this case, if I made a mistake when entering data (and I'd find out the mistake only after I have finished running the script and generated a file), I'll have to correct the mistake and run the script again, thus, the existing file. Maybe I can have the code check for existing file and delete it first if it exists.

But that's not the point. The point is: shouldn't saveas overwrite the existing file if there is one? I have such impression because many people have asked how to suppress the overwrite confirmation message box. Since I have in my code "Application.DisplayAlerts = False", I never thought existing file would be a problem. And I just ran the script three times with an existing file and I did not encounter a problem. Hence, I believe something else is causing the problem.

 

[xenou]

Yes, well, it's difficult to remotely debug code that *should* work but *doesn't*. So you are probably right that it's "something else". My only suggestion is to code defensively, don't try to to do much all at once, and keep it simple. There are lots of things that can happen - for instance, if you are overwriting files, the file you are trying to overwrite could be in use. This is why you should always be careful when dealing with aspects of your code that could be subject to failure.

I typically push these out to a function (or at least enclose them in an inline error handling block) and have my program prepared to take an alternate course of action if the attempt fails: choose an alternative file name, have the user provide a filename, save to an alternate location, abort ... whatever seems best to you. To the extent possible, try to resolve all such potential problems first rather than finding out halfway through your routine that it won't be able to finish as you'd planned it to.