[AccessD] DoCmd.TransferSpreadsheet tip

[Stuart McLachlan]

Apparently so.  The parameter name is Range and it was originally limited to imports of 
ranges from Excel. 

It looks as though the poorly documented extension of the function's Export capabilities to 
allow exporting named ranges and sheets  still has the Excel Range  restrictions coded into 
it. (From my reading, it has actually been thus  for many versions of Access and Excel)


And yes, once you have done the export, there is nothing to stop you from renaming sheets 
in a workbook, the limitation is only in the VBA Docmd.TransferSpreadsheet function, it's 
not an Excel limitation.

On 22 Jun 2023 at 7:13, Gustav Brock via AccessD wrote:

> 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.tran
> > > sfer 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
>