How to open up a new Excel Workbook in MS Access VBA that's of the newer format?

0
votes

I am using MS Access and Excel 2010.

In MS Access VBA, I create/open new workbooks like so:

Set objXl = CreateObject("Excel.Application") 
Set xlWBk = objXl.Workbooks.Add

And despite having Excel 2010, for whatever reason this workbook only goes up to 65535 rows, I assume because for some reason it defaults to .xls when making a new book or something.

It's only when I save as xlsx/close/reopen it do I get the full 1-million plus rows:

xlWBk.SaveAs someFilePath, FileFormat:=xlOpenXMLWorkbook 'xlsx format AFAIK
xlWBk.Close
Set xlWBk = objXl.Workbooks.Open(someFilePath)

Is there a way to default the creation of a new Excel workbook to be in .xlsx format with the full number of rows without needing to save/close/reopen it?

Edit: In my case, changing the default save method in Excel's options doesn't seem to be sticking for whatever reason. New workbooks always want to open up in Compatibility Mode.

1
excelms-accessvbams-access-2010
What is your default save format set to in Excel's options? – Rory
@Rory Oh man, 97-2003... is that all it is?! – user525966
Unfortunately not, it didn't solve the problem even after changing the default save option to .xlsx :( – user525966
Does it default to .xls both when creating and opening the file? – Mr ML
Maybe you can try to change objXl.DefaultSaveFormat before creating the workbook? – Vincent G

1 Answers

2
votes

You can set the default format before creating the workbook:

Set objXl = CreateObject("Excel.Application")
objXl.DefaultSaveFormat = xlOpenXMLWorkbook ' or xlOpenXMLWorkbookMacroEnabled
Set xlWBk = objXl.Workbooks.Add