[AccessD] DoCmd.TransferSpreadsheet tip

[Jim Dettman]

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