Rendering SQL As Excel Spreadsheet?

[PeteCresswell]

I've got some SQL that returns a RecordSet.

Instead of showing the RecordSet, I would like to create an Excel
spreadsheet based on said RecordSet.

No cosmetics, no beautification, no formatting; just a quick-and-dirty
spreadsheet.

Seems like Docmd.TransferText sb the vehicle, but I can't make it work
by feeding it SQL - only a table name.

I *know* I've done this in the past, but cannot recall how.

Or am I just wishing?

 

[PeteCresswell]

No cosmetics, no beautification, no formatting; just a quick-and-dirty
spreadsheet.

What I was really thinking was to do this without creating a recordset
based on the SQL, instantiating an instance of Excel, and looping
through the recordset, populating rows in the Excel instance's
workbook, saving the workbook, and so forth.

 

[Damon Heron]

How about this:

DoCmd.TransferSpreadsheet acExport, 8, "qrySumofPayments", "C:\NewXL.xls"

This transfers a saved query to an excel 2000 or newer spreadsheet.

Damon

 

[Jeff Boyce]

Consider the possibility of eliminating the MS Access step and simply doing
this from Excel itself, directly.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[PeteCresswell]

DoCmd.TransferSpreadsheet acExport, 8, "qrySumofPayments", "C:\NewXL.xls"

That seems tb the ticket.

It even resolves query references to fields on forms - which is a
tripping point in other VBA approaches - like opening up a recordset
and iterating through it.

Thanks.