[AccessD] DoCmd.TransferSpreadsheet tip

Ryan W wrwehler at gmail.com
Fri Apr 21 12:25:19 CDT 2023


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:36 AM 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.transferspread
> 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
>


More information about the AccessD mailing list