Access VBA Syntax for Excel Output

[The_Real_Phil_S]

Hi Guys

I am trying to update an Access application.

Currently there is VBA code included to output results in Excel format.

The application is old so the output files produced are in an old Excel format and have the xls file extension.

The typical code that initiates the output is given below:

DoCmd.TransferSpreadsheet acExport, 8, "exceltemp", "G:\DM_Report_Test\temp.xls"

I am thinking that the number 8 indicates the version of Excel produced and this is reinforced by the xls extension.

How do I update this code to produce a later version of Excel that works with the xlsx format?

Our company is running Office365 and my machine reports version (16.0.11929.20436) if that gives an indication of the application that will be used to view the output files.

Many thanks

Phil

[#2 · Accepted solution]

To export as .xlsx file use one of the following:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "exceltemp", "G:\DM_Report_Test\temp.xlsx"

OR 

DoCmd.TransferSpreadsheet acExport, 10, "exceltemp", "G:\DM_Report_Test\temp.xlsx"

[#3]

Great Stuff thanks @isladogs 

Works a treat

Phil

[#4]

You're welcome

[#5]

mdsarfarazalam723@gmail.com