workbooks.add as .xlsm instead of .xls?

[winglessbuzzard]

Is there any way in VBA to create a new workbook with the extension of .xlsm instead of .xls (comparability mode) in excel 2007?

Workbooks.add has an option to create two excel4 workbook versions, a chartsheet, and a regular worksheet... but doesn't seem to have an option for the type of file to create.

My reason for wanting this is that I have a code that builds a table that goes beyond 65k rows... and I don't want to go through the hastle of breaking the table up programatically or save-as'ing the new workbook as .xlsm, closing it, then reopening it (programatically).

It seems there should be some way to specify the extension of a file you are creating upon its creation rather than on it's save.

 

[VoG]

In Excel Options in the Save category you can specify the default file type. That should also define the file type when you save an added workbook.

 

[winglessbuzzard]

Awesome... thanks!!

 

[SeaDragon]

This can be set programmatically by manipulating the Application.DefaultSaveFormat property before invoking Workbooks.Add.

Here's a link with some help on the types - http://msdn.microsoft.com/en-us/library/bb241279(v=office.12).aspx

Excel8 is a 1997-2003 .xls. I figure you're looking to use xlOpenXMLWorkbookMacroEnabled.