VBA to Export Access Select Queries to Excel Files- Error

[aravindhan_31]

Hi, I wanted to export the result of the query to an excel & I have the below vba code which exports as an excel file, but I am not able to open the excel file it says file extension not valid
code taken from http://www.mrexcel.com/forum/micros...export-access-select-queries-excel-files.html

Private Sub ExportExcel()
 
    Dim i As Integer
    Dim myQueryName As String
    Dim myExportFileName As String
        myQueryName = "Result1"
        myExportFileName = "C:\Users\aravindhan.jayaraman\Desktop\ACAT" & myQueryName & ".xlsb"
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, myQueryName, myExportFileName, True
   
 End Sub

can someone helpe me pelase

 

[Norie]

As far as I recall acSpreadsheetTypeExcel9 is for Excel 2000 files, ie files with the xls extension.

Try using acSpreasheetTypeExcel12 (or acSpreadsheetTypeExcel12Xml) and change xlsb to xlsx.

 

[aravindhan_31]

Hi,
Thanks,
I am using 2016 version

 

[Norie]

So am I.

 

[aravindhan_31]

so, if excel 2016 what number should be changed? can u help me with that please?

 

[Norie]

Like I said, change acSpreadsheetTypeExcel9 to acSpreasheetTypeExcel12 or acSpreasheetTypeExcel12Xml and change the file extension from 'xlsb' to 'xlsx'.