run-time error '1004' Cannot save file on some computers

[jay_gill]

I am having some issues with a macro that I wrote, when trying to save a file on some users computers.

The macro has to run in Excel 2007 (or newer) and check for this condition at the outset.

The troublesome part of my marco occurs when it copies a sheet from an excel 2003 file (.xls) and tries to save as a 2007 file (.xlsx). One particular user keeps getting the error:
"Run-time error '1004'
Microsoft Office Excel cannot access the file 'c:\c4c320000'"
The c:\c4c320000 always seems to change and is never the same as the filename variable.

I'm wondering if this truly a macro issue, or some setting in Excel/Windows?

The code is:
'Define temp file name
benchmark_name_temp = ws_name & "_" & Replace(fire_file_name, ".xls", "") & ".xlsx"
benchmark_name_and_path_temp = "c:\" & benchmark_name_temp

'Save as Excel 2007 file, close and reopen so 1 million rows are available for data
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs filename:=benchmark_name_and_path_temp, _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
Application.DisplayAlerts = True
ActiveWorkbook.Close savechanges:=False
Workbooks.Open filename:=benchmark_name_and_path_temp

Thanks,

 

[jay_gill]

come to find out that depending on where a user sits (geographically in different offices) they may not have rights to their C:\ drive. Didn't even cross my mind that this was a possibility.

 

[Glory]

Weird. How did you get around it?