Cannot save to desktop

[bikeordie1]

at the end of my code is:
activeworkbook.saveas "C:\users\" & environ("Username") & "\Desktop\GRN Report - " & format(date, "MM-DD-YYYY")

Now this works fine for myself and a few other users in the office (all the same platforms - win7)

except for 2 users which get this error:

run time error '1004':
Microsoft excel cannot access the file:
'C:\Users\john.doe\Desktop\0AB3D100'. There are several possible reasons:
the file name or path does not exsit
the file is in use
workbook you are trying to save has the same name

the part in red is different every time the error comes up. like a temp file or something. I cannot for the life of me find the issue here. Any thoughts?

 

[chuckchuckit]

Try putting the location full path into a string first (so there are no amperstand or quote characters), then use that string as your final path such as:

activeworkbook.saveas TheStringPath

 

[gsistek]

It's also good to include the file extension (.xls, .xlsm, .xlsx). I've run into problems with that before.

 

[bikeordie1]

Tried making the path and then referencing that. no go. also with the extension included.

When i pre-assemble the path it all looks correct if i hover over it during pause when the error happens. it looks perfect. yet that random number shows up in the error box right over top of it

 

[chuckchuckit]

Try taking out the minus sign "-" which paths don't like.

 

[chuckchuckit]

If your launching macro key combo has a "Shift" in it, that can play havoc with workbook routines which can randomly result in a run time error 1004 (due to a bug in MS). Such as if you start macro with "Ctrl-Shft-R" change it to Ctrl-R instead and see if that does it.

 

[bikeordie1]

No luck with the minus signs...

chuckchuckit - no key combo, the macro is called from Workbook_Open

 

[chuckchuckit]

Is the macro routine that calls "Workbook_Open" launched from a key combo using "Shift" ? As it is especially Workbook_Open that runtime error 1004 has trouble with. It can happen randomly much later in a program usually involving opening a workbook if the macro uses Shift key to start the macro.

 

[chuckchuckit]

You can test this by starting the macro from your menu in macros view then run from there. If everything works from there, then the "Shift" key is the problem.

 

[bikeordie1]

no shift keys involved. if i run the macro from the menu, or let it initiate from the workbook_open calling it then it has the same result.

Frustrating...