How do I make WorkBooks.Add create an Excel 2007 workbook

[sbendbuckeye]

Hello all,

In an Excel 2007 workbook, I have the following code:

Dim sheet As Worksheet
Set sheet = WorkBooks.Add

Reference is set to Office 12

This creates an Excel 2003 file with 65,000+ rows and 256 columns. I need it to create an Excel 2007 file because the data exceeds 70,000 rows. How do I make Excel create an Excel 2007 workbook? Thanks in advance for any ideas and/or suggestions!

 

[Blade Hunter]

Sounds like you are adding a sheet to a 2003 workbook, convert the workbook to a 2007 and try it.

 

[sbendbuckeye]

Hello Blade,

Thanks for the response. Unfortunately, it is already an Excel 2007 workbook. It is a macro enabled xlsm file. This is very annoying. I wonder if it is a bug. Are there special objects one needs to use when using Excel 2007?

 

[sbendbuckeye]

For the benefit of others, here is the solution. In Excel Options, select the Save tab. The top combo box in Save Workbooks is for the save file format. If you set it to Excel 97-2003 Workbook (*.xls) then Workbooks.Add will create an Excel 2003 workbook. If you set it to Excel Workbook (*.xlsx) then Workbooks.Add will create an Excel 2007 workbook.