Workbooks.Add (force XLSX format)

[shockandawe]

I have an existing program with the following line of code...

Set NewWB = Workbooks.Add

I'm using Excel 2007.

When I execute the code above, Excel adds a new workbook in compatibility mode (the workbook has 65,536 rows of data).

How can I tell VBA that I want the new workbook to be XLSX format? I don't want to save the file as xlsx and open it up again as this is part of a loop and would result in saving/opening hundreds of files.

Thanks.

[mancubus]

your DefaultSaveFormat is Excel 97-2003

you can cange it from Office button, options, save

or if you want 97-2003 as default and add new workbook as xlsx then


Dim SaveFormat As Long
'Remember the users setting which currently is 97-2003 file format
SaveFormat = Application.DefaultSaveFormat
'Set it to the 2007-2010 file format xlsx
Application.DefaultSaveFormat = 51
Workbooks.Add
'Set DefaultSaveFormat back to the users setting
Application.DefaultSaveFormat = SaveFormat



http://www.rondebruin.nl/checkcompatibility.htm
http://www.rondebruin.nl/saveas.htm