SOLVED

Access VBA Syntax for Excel Output

New Contributor

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

 

 

 

 

4 Replies
best response confirmed by The_Real_Phil_S (New Contributor)
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"

 

Great Stuff thanks @isladogs 

 

Works a treat

 

Phil

You're welcome
mdsarfarazalam723@gmail.com