[AccessD] DoCmd.TransferSpreadsheet tip

[Stuart McLachlan]

It's a bit more complicated than that.

It's not just leading digits. 
The same happens with short strings that end with a digit. May23 gets an underscore, but 
March23 doesn't


On 21 Jun 2023 at 11:51, Gustav Brock via AccessD wrote:

> 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.transfer
> spreadsheet
> 
> *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.* -- --
> AccessD mailing list AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd Website:
> http://www.databaseadvisors.com
>