[AccessD] DoCmd.TransferSpreadsheet tip

[Bill Benson]

Even if you had only output to flat excel files VBA could have combined
them.

Watch out for 30 character limitation for sheet names (and may be less
depending on how VBA causes the sheet tab name to get created), and
unusable characters if sheet names are created from data dynamically.

On Fri, Apr 21, 2023 at 12:17 PM Ryan W <wrwehler at gmail.com> wrote:

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