[AccessD] DoCmd.TransferSpreadsheet tip

[Gustav Brock]

Hi Stuart

So, the passed name must follow the rules for a range name, not a worksheet name, even though that's what being created? 

A worksheet, however, you can (re)name to a single digit:

? ThisWorkbook.Worksheets(1).Name
1

/gustav

-----Oprindelig meddelelse-----
Fra: AccessD <accessd-bounces+gustav=cactus.dk at databaseadvisors.com> På vegne af Stuart McLachlan
Sendt: 22. juni 2023 03:50
Til: Access Developers discussion and problem solving <accessd at databaseadvisors.com>
Emne: Re: [AccessD] DoCmd.TransferSpreadsheet tip

Worked it out.  Using the Range parameter to define the spreadsheet name applies the standard Range name conventions. There is no getting around that.

i.e. 
It  must start with a letter or an underscore. If not it will get a preceeding underscore.

It can be any single alpha character except R or C

It cannot be interpretable as a cell value. Column names range from A to XFD so any one to three letters in that range followed by any number up to 1048576 will get a leading underscore.

It can't contain any non alphanumeric character other that period,question mark, underscore and backslash .All invalid characters are replaced by underscores.

If it conflict with any existing name, the existing sheet will be replaced.



On 22 Jun 2023 at 0:07, Stuart McLachlan wrote:

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