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

sbendbuckeye

Active Member
Joined
Nov 26, 2003
Messages
440
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!
 
Last edited:

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Blade Hunter

Well-known Member
Joined
Mar 13, 2008
Messages
3,147
Sounds like you are adding a sheet to a 2003 workbook, convert the workbook to a 2007 and try it.
 

sbendbuckeye

Active Member
Joined
Nov 26, 2003
Messages
440
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

Active Member
Joined
Nov 26, 2003
Messages
440
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,569
Messages
5,596,912
Members
414,110
Latest member
docops

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
Top