[AccessD] DoCmd.TransferSpreadsheet tip

[Gustav Brock]

Hi Stuart

Ah, right you are, I can see. Now the naming restrictions make sense.
And renaming the worksheet has nothing to do with the NamedRange, of course.

/gustav

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

Just picked up another factoid. If it's not a current range name, not only does the sheet get created with the range name, but a named range is also created for the data (Hit Ctrl+G in the spreadsheet and you will see the named ranges. )

Just found a good reference that points out a few other foibles   It's well worth a read:

http://www.accessmvp.com/KDSnell/EXCEL_Export.htm#ExpRange



On 22 Jun 2023 at 12:21, Stuart McLachlan wrote:

> Version2 (I haven't seen my previous similar post appear) Worked it
> out:
>  If there is a named range in the workbook you are exporting to, your 
> data will be placed in that range.
> If there is no such named range, a new sheet will be created with that 
> name. Using the Range parameter always 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 21 Jun 2023 at 13:55, Gustav Brock via AccessD wrote:
> 
> > Hi Ryan
> > 
> > I tried that, also prefixing/suffixing with various signs (#, $, !) 
> > and quotes, but either error or no effect.
> > 
> > /gustav
> > 
> > -----Oprindelig meddelelse-----
> > Fra: AccessD <accessd-bounces+gustav=cactus.dk at databaseadvisors.com>
> > På vegne af Ryan Wehler Sendt: 21. juni 2023 15:33 Til: Access 
> > Developers discussion and problem solving 
> > <accessd at databaseadvisors.com> Emne: Re: [AccessD] 
> > DoCmd.TransferSpreadsheet tip
> > 
> > That´s good to know. I wonder if casting the value using Cstr() 
> > would remedy that? I wonder if it´s being treated like an integer 
> > and that´s causing a side effect?
> > 
> > > On Jun 21, 2023, at 7:02 AM, Gustav Brock via AccessD 
> > > <accessd at databaseadvisors.com> 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

<snip>