vba to Export Multiple Sheets to xlsx File

[jhrBanker]

I'm using Excel 2007. For years I've been using the following code to export to xls.

Private Sub cmdExport1_Click()
On Error GoTo Err_cmdExport1_Click
Dim strFileName As String
Dim strFilter As String
Dim strFileDesc As String
Dim strFileExt As String
lblExport1.Visible = False
strFileDesc = "Excel File (*.xls)"
strFileExt = "R3*.xls"
strFilter = ahtAddFilterItem(strFilter, strFileDesc, strFileExt)

strFileName = ahtCommonFileOpenSave( _
InitialDir:="C:\Users\myusername\Documents\My SAS Files\9.3\R3\OT", _
Filter:=strFilter, _
OpenFile:=False, _
DialogTitle:="Save file as ... ", _
Flags:=ahtOFN_HIDEREADONLY)

DoCmd.RepaintObject acForm, "FrmMainMenu"
DoCmd.Hourglass True

' 1 - Export exp_INS_Combos
DoCmd.TransferSpreadsheet _
acExport, _
acSpreadsheetTypeExcel9, _
"exp_INS_Combos", _
strFileName, _


True, _
"INS_Combos"

' 2 - Export exp_INSData_M2
DoCmd.TransferSpreadsheet _
acExport, _
acSpreadsheetTypeExcel9, _
"exp_INSData_M2", _
strFileName, _
True, _
"INSData_M2"

' 3 - Export exp_OM_Output
DoCmd.TransferSpreadsheet _
acExport, _
acSpreadsheetTypeExcel9, _
"exp_OM_Output", _
strFileName, _
True, _
"OM_Output"

' 4 - Export exp_SAP_Maps
DoCmd.TransferSpreadsheet _
acExport, _
acSpreadsheetTypeExcel9, _
"exp_SAP_Maps", _
strFileName, _
True, _
"SAP_Maps"

' 5 - Export exp_Sum_Merge
DoCmd.TransferSpreadsheet _
acExport, _
acSpreadsheetTypeExcel9, _
"exp_Sum_Merge", _
strFileName, _
True, _
"Sum_Merge"

DoCmd.Hourglass False

MsgBox "Export to Excel is Complete" & vbCrLf & _
"FileName: " & strFileName & vbCrLf & _
"Five (5) Sheets Created ", _
vbInformation, "File Export Status: "
lblExport1.Visible = True

Exit_cmdExport1_Click:
Exit Sub

Err_cmdExport1_Click:
' MsgBox Err.Description
DoCmd.Hourglass False
Resume Exit_cmdExport1_Click
End Sub

I need to modify the code to export to xlsx, since some of the sheets now have more than 66k lines.
I've changed acSpreadsheetTypeExcel9 to acSpreadsheetTypeExcel12, but am unable to open the xlsx file that's created (Error: file format or file extension is not valid).
Which TypeExcel should I use?

[June7]

The code still shows the old references for Excel9 and file extension .xls. What did you change these to - Excel12 and .xlsx? I did a test with Excel12 and .xls. The export works. I do get an error message when I open the workbook but I just respond Yes to open and all the data is there in the bigger sheet. Then do SaveAs.

[jhrBanker]

The code still shows the old references for Excel9 and file extension .xls. What did you change these to - Excel12 and .xlsx?

I wrote the following after the code, so maybe you didn't notice it:

I need to modify the code to export to xlsx, since some of the sheets now have more than 66k lines.
I've changed acSpreadsheetTypeExcel9 to acSpreadsheetTypeExcel12, but am unable to open the xlsx file that's created (Error: file format or file extension is not valid).
Which TypeExcel should I use?

The acSpreadsheetTypeExcel12 didn't work.

However, I have since gotten it to work by replacing the acSpreadsheetTypeExcel9 with just the number 10, like this:

DoCmd.TransferSpreadsheet _
acExport, _
10, _
"exp_INS_Combos", _
strFileName, _
True, _
"INS_Combos"

Thanks,
jr

[June7]

Interesting, I think 10 is supposed to be Excel 2002.