[AccessD] DoCmd.TransferSpreadsheet tip

[Gustav Brock]

Hi Ryan

This is the best tip I've seen in years. Thanks!

I see one issue though. If you pass a range/worksheet name with a leading digit, for example "2023", the worksheet will be given that name plus a leading underscore: "_2023".
In general, Access silently replaces spaces and special characters with underscores, so this behaviour indicates, that a space or something has been prefixed and then replaced.

Are you aware of a workaround?

The "method" I've found is to use a name having a letter as the first character, like: "Balance 2023".
This results, as expected, in the worksheet name "Balance_2023", which is acceptable.

/gustav


-----Oprindelig meddelelse-----
Fra: AccessD <accessd-bounces+gustav=cactus.dk at databaseadvisors.com> På vegne af Ryan W
Sendt: 21. april 2023 18:17
Til: Access Developers discussion and problem solving <accessd at databaseadvisors.com>
Emne: [AccessD] DoCmd.TransferSpreadsheet tip

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.*
--