[AccessD] DoCmd.TransferSpreadsheet tip

[Stuart McLachlan]

I didn't know this either.  Great tip, thanks!

On 21 Apr 2023 at 12:25, Ryan W wrote:

> Jim,
>   Hey sometimes it pays to be lazy. ;)
> 
>   Glad to know I'm not the only one who didn't know about this.  Maybe
>   the
> docs can be updated for accuracy.
> 
> 
> On Fri, Apr 21, 2023 at 11:36AM Jim Dettman via AccessD <
> accessd at databaseadvisors.com> wrote:
> 
> >
> > Wow...that's nice.
> >
> >  Thanks for sharing!
> >
> > <<.  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.>>
> >
> >  Not as hard as you would think, but you do need to know the Excel
> >  object
> > model to an extent.   But you are right, this is far easier.
> >
> > Jim.
> >
> > -----Original Message-----
> > From: AccessD On Behalf Of Ryan W
> > Sent: Friday, April 21, 2023 12:17 PM
> > To: Access Developers discussion and problem solving
> > <accessd at databaseadvisors.com>
> > Subject: [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
> > erspread sheet
> >
> > *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
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > https://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com