[AccessD] DoCmd.TransferSpreadsheet tip

[Gustav Brock]

Hi John

Yes, many of us have worked out similar methods, but in this case only figures are needed with no fancy formatting, so replacing it all with a single line of code ... uh, I couldn't resist and saved a lot of time.

/gustav

-----Oprindelig meddelelse-----
Fra: AccessD <accessd-bounces+gustav=cactus.dk at databaseadvisors.com> På vegne af John Colby
Sendt: 21. juni 2023 15:54
Til: Access Developers discussion and problem solving <accessd at databaseadvisors.com>
Emne: Re: [AccessD] DoCmd.TransferSpreadsheet tip

It was all long ago that I did this, however what I did in the past is to create a template spreadsheet, formatted as I liked, then build code in the spreadsheet to do whatever I needed.  IIRC I had the code reach out to the access database and 'pull' the data into the spreadsheet.  I then executed the code itself from Access to trigger the whole thing.

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


--
John W. Colby
Colby Consulting