Excel 2007 Macro Issue

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

bacalhoun

Thread Starter
Joined
Dec 28, 2001
Messages
2
I have researched my issue but other threads posted here did not solve my problem. I have an Excel 2007 spreadsheet that was first designed in Excel 2003, and it has a macro that generates 93 spreadsheets with prepopulated data. The macro works, it generates the files but then I cannot open them due to the following error:
'Excel cannot open the file 'xxx.xlsx' because the file format or file extension is not valid.'
I have tried changing the macro to save as .xlsm, but get the same result. I have conditional formatting that will not work in Excel 2003, plus most of the people who will be opening the file are on Excel 2007, so I don't want to save in xls format; however, I did change the macro to see if it worked for Excel 2003 and it does.

Sure hope someone can help.

Here is a sample from the macro:

Range("B1:I1").Select
ActiveCell.FormulaR1C1 = "HP Argentina"
Range("C2").Select
ActiveWorkbook.SaveAs Filename:= _
"C:\Data\P2012 Templates\P2012 Country Summary and Cost Forecast - HP Argentina.xlsx" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
 
Joined
Sep 4, 2003
Messages
4,912
If the macro is being run in 2007 you should first try changing the FileFormat value that you are passing. Change the portion below.

FileFormat:=xlNormal
So that it reads

FileFormat:= xlOpenXMLWorkbook
If the workbook is a macro enabled workbook use the line below instead.

FileFormat:= xlOpenXMLWorkbookMacroEnabled
Rollin
 

bacalhoun

Thread Starter
Joined
Dec 28, 2001
Messages
2
Hi Rollin, the first option works very well, the macro produces the spreadsheets, and I can open them without error. Thank you for the quick reply, I really appreciate it.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top