[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
> > > 
> > > 
> > > -----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.tran
> > > sf erspreadsheet
> > > 
> > > *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.*