[AccessD] DoCmd.TransferSpreadsheet tip

[Stuart McLachlan]

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