Over the last week or so I got help implementing multiple worksheets in Excel via Access 97. Everything was working great - transferring data to many worksheets.
Now, the task was to move to Office 2000 and XP, and guess what? - it doesn't work for either version!!!
I've been using TransferSheet from within Access, and even though Help says you are not suppose to specify a Range when exporting - it works great in Access 97.
I use the following code in a loop for each transfer:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tblExportLinks", sDestinationFile, 0, sSheet & "AA1:AW100
I've tried all Excel types, etc. Does anyone know how to export data out to multiple worksheets for 2000/XP?