VB[A] Way to force new workbook to be in xlsx format?

[ou81aswell]

I'm automating Excel through COM.

I have a user who is running Excel 2010. His version of Excel is set to save files in the Excel 97 - 2003 format (*.xls) which is limited to 65,536 rows.

I have a piece of code that produces a recordset from a database and then writes it to a new sheet. Often, there are more than 65,536 rows in it.

On this user's machine my code fails when it blows past the 65,536 row limit because the new workbook it creates is in .xls format instead of .xlsx format.

Is there a way for my code to force Excel to create the new workbook in xlsx format?

 

[Andrew Poulsom]

You can (temporarily) change the Application's DefaultSaveFormat property.

 

[Jon von der Heyden]

I believe you should be using the Workbook FileFormat property in your code, but hard to suggest exactly without seeing the component of the code that creates the new workbook. From Excel help:

Workbook.FileFormat Property (Excel)

Returns the file format and/or type of the workbook. Read-only XlFileFormat.

Syntax
expression.FileFormat

expression A variable that represents a Workbook object.

Remarks
Some of these constants may not be available to you, depending on the language support (U.S. English, for example) that you've selected or installed.

Example
This example saves the active workbook in Normal file format if its current file format is Excel 97/95.

VBA

If ActiveWorkbook.FileFormat = xlExcel9795 Then

    ActiveWorkbook.SaveAs fileFormat:=xlExcel12

End If

 

[ou81aswell]

That sounds like just the thing. Thanks!