Docmd.Transferspreadsheet

[RayMilhon]

I have an Access 2010 Database that was created a few years ago that runs automated and executes a query exports that query to excel and emails the excel file to the people that need this data.



The original VBA used the Docmd.transferspreadsheet function to export the query. Is there a newer function that allows the export to export to Excel 2010 format. the best the Docmd does is Excel 2000?

[aytee111]

It is in the spreadsheet type in the DoCmd statement, you specify which version of Excel.

[June7]

The highest version offered is acSpreadsheetTypeExcel12. However, I find that the xlsx extension does not work. Have to use xls. Was this fixed in 2013 or 2016?

[ssanfu]

An AcSpreadSheetType of "acSpreadsheetTypeExcel12" is for A2007. (with xlsx extension)
For A2010, use an AcSpreadSheetType of "acSpreadsheetTypeExcel12Xml" (with xlsx extension)

https://ss64.com/access/acspreadsheettype.html

[June7]

Rats! I knew I should have tested the Xml.

Steve, did you mean to say: A2007. (with xls extension) ?

[ssanfu]

From what I have read and understand, "acSpreadsheetTypeExcel12" is for A2007. (with xlsx extension)

If you use "acSpreadsheetTypeExcel12" in A2010, there will be or seems to be problems opening the Excel workbook.
I looked at a lot of posts and using "acSpreadsheetTypeExcel12" in A2007 seemed to work, but in A2010 caused problems opening the workbook.

I'll look to see if I can resurrect a confuser with A2007 to do some testing....


I started using "Docmd.OutputTo" instead of the Docmd.transferspreadsheet function. Then I use automation to format the Excel worksheet. I like it better.....
The command for exporting a query to Excel worksheet looks like:

DoCmd.OutputTo acOutputQuery, "EQ_StatementMonthlyAll", acFormatXLSX, strSaveFileName, False

I use folder picker code to select the folder where I want to save the file, then call the code to do the formatting and shazam!! Its done.