Error 1004 trying to save active workbook

littlebob

New Member
Joined
Apr 24, 2011
Messages
6
I have a macro that runs fine using Excel 2007 on Windows XP, but with Excel 2010 on Windows 7 I get a 'Runtime error 1004' from the following line of code:

ActiveWorkbook.SaveAs Filename:="Teams-" & Range("A2").Value, _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

Am I overlooking something simple?
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Perhaps I'm missing something in the above answer, but the failing SaveAs statement does have the FileFormat specified. This isn't a 2003 vs 2010 issue, the code runs on Excel 2007 but not on 2010.
 
Upvote 0
Does the filename you are trying to save as have the correct extension?
 
Upvote 0
I guess I'm not posting my problem/question properly. (I'm new at this)
The problem is that a line of code in one of my macros runs fine on my desktop (running XP and Excel 2007) but gives me an error 1004 when I try to run the same macro on a system running Windows 7 with Excel 2010. I guess my question is: Is there a compatibility issue between Excel 2007 and Excel 2010 that would prevent the following line of code to not work? :

Sub prep_update()
'
' prep_update Macro
'
'
(Lots of code between here and the failing instruction, and all work fine.)

(THE FAILING INSTRUCTION FOLLOWS)

ActiveWorkbook.SaveAs Filename:="Teams-" & Range("A2").Value, _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

ActiveWorkbook.Close

End Sub
 
Upvote 0
Try adding the file extension

Rich (BB code):
ActiveWorkbook.SaveAs Filename:="Teams-" & Range("A2").Value & ".xlsm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
 
Upvote 0
OK, I feel like a COMPLETE idiot. :( The error code was telling me what the problem was. There was an invalid character :)) in the cell I was using as part of the SaveAs FileName. Of course, that character was not in the cell being used in the macro that worked.
Thanks to all who posted helpful ideas. :)
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,559
Latest member
MrPJ_Harper

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