run-time error '1004': incorrect function - ActiveWorkbook.SaveAs

[yky]

I'm having a strange problem. When I run a VBA script, Excel gives me an error message saying "run-time error '1004': incorrect function".

The strange thing is I repeatedly do everything consistently (open the xlsm, run the script which displays a userform, enter a date, the same date, that is all) and get the error one out of every four times (roughly), not every time.

If the problem is due to incorrect function, shouldn't I get the error every time?

Even stranger, if I add a msgbox right before the problem code, now, I consistently get the error message "incorrect funcction".

Any idea? Thanks.
================================
msgbox("activeworkbook name is " & ActiveWorkbook.Name) <- getting consistent error message if added

ActiveWorkbook.SaveAs Filename:=destinationPath & destinationFileName, FileFormat:= _
xlWorkbookDefault

'where destinationPath is "\\lenovo-pc1\d\deddem\" and
'destinationFileName is "test". I ran the script on lenovo-pc1.

 

[mikerickson]

Perhaps removing the parenthesis:

MsgBox "activeworkbook name is " & ActiveWorkbook.Name

 

[yky]

Perhaps removing the parenthesis:

MsgBox "activeworkbook name is " & ActiveWorkbook.Name

Thank you for the reply.
My main concern is why the saveas occasionally fails. I don't need the msgbox. It will be removed if the code has no problem.

 

[MarkE]

Wwere you able to get an answer to this? I'm having the same problem. Thanks

 

[mikerickson]

Are you always having a path seperator between the path and the name of the workbook?
Are you getting the errors on the same machine or might it be a Windows/Mac issue?

 

[MarkE]

It's a program I wrote in Excel 2003 about 5 years ago. It worked fine there.

Now, in Excel 2007, it's throwing the error... looks like it's probably been doing it for a while, and Accounts Payable never told me until now when something else came up.

Mac is not involved - Windows only.

I tried taking the spaces out of the network directory path. It didn't help.

Earlier this year, I ran into another product defect where Excel 2007 was only able to run one networked Microsoft Web Query - if you ran more, it would get the 1st query's results. Microsoft confirmed that and logged it.

So I was thinking maybe this was a network issue, but I tried the C:\ drive and then another spot on the network, and it worked fine.

I've just noticed that even though it says it's erroring, it actually has already done the save at that point. The file has been saved with the correct new name and format, and then it yells and claims to have had a problem at that line.

So it's throwing something on the way out of the SaveAs routine.

Web searches indicate numerous people have had thi sproblem... though the other ones posted say it's intermittant. Mine's constant at that location.

 

[MarkE]

in case this means anything to anybody, if I ask it to show me info on the error, Msgbox Err.Source tells me that the source is file msxml5.dll , for whatever good that does.

 

[MarkE]

It turns out that it was throwing the error because I did have an invalid directory. But what happened to the clearer to read message explaining that I had a bogus directory? Instead of just claming "Invalid function." in a nondescript manner?