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

yky

Well-known Member
Joined
Jun 7, 2011
Messages
1,881
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Perhaps removing the parenthesis:
Code:
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,733
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top