According to MS on TransferSpreadsheet: "Access creates a new spreadsheet when you export data from Access. If the file name is the same as the name of an existing spreadsheet, Access replaces the existing spreadsheet, unless you're exporting to an Excel version 5.0 or later workbook. In that case, Access copies the exported data to the next available new worksheet in the workbook."
However, MS also says can edit linked spreadsheet with Access but I have never been able to do that.
Interesting, that doesn't appear to be the behavior I am experiencing.
My template has just 1 sheet named "Data", and I set the first three column widths.
Here is my export block, that copies the template then exports to that copy:
Code:
' Copy Excel template to export location
exportFile = exportPath & fName & ".xlsx"
FileCopy exportTemplate, exportFile
' Export query to Excel table
DoCmd.TransferSpreadsheet acExport, 10, "qry75-Groups_Members_By_Mgr_EXPORT", exportFile, True, ""
It exports the data to that file, on the Data tab, and my column widths are what I set in my template. So it is not creating any new worksheets in my workbook.
(So it is working exactly as I would have hoped, though seems to be different than quote you cited).