Rendering SQL As Excel Spreadsheet?

  • Thread starter Thread starter PeteCresswell
  • Start date Start date
P

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?
 
No cosmetics, no beautification, no formatting; just a quick-and-dirty
spreadsheet.
Click to expand...

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.
 
How about this:

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

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

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

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
DoCmd.TransferSpreadsheet acExport, 8, "qrySumofPayments", "C:\NewXL.xls"
Click to expand...

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.
 
Back
Top