[AccessD] DoCmd.TransferSpreadsheet tip

[Ryan W]

I was looking for a way to export some queries to a spreadsheet, the client
wanted 3 worksheets in the workbook.

Despite what the docs say about specifying a range argument for exports
causes it to fail, if you specify a string rather than a range it WILL
create the excel file and the sheet will be named what you specify in the
range.

Subsequent TransferSpreadsheet commands to the same file with a different
range name will add that data to a new worksheet.

I wish I'd known this some time ago as I've had to tell our clients on
occasion that we can only provide "flat" excel files.  I didn't want or
have the knowledge to build a spreadsheet routine that used COM to try and
stuff data into worksheets on a workbook, so this makes it super convenient
and easy.




:
https://learn.microsoft.com/en-us/office/vba/api/access.docmd.transferspreadsheet

*Range* Optional *Variant* A string expression that's a valid range of
cells or the name of a range in the spreadsheet. This argument applies only
to importing. Leave this argument blank to import the entire spreadsheet.
When you export to a spreadsheet, you must leave this argument blank. *If
you enter a range, the export will fail.*