Export result of SQL query to Excel

  • Thread starter Thread starter Jonathan Blitz
  • Start date Start date
J

Jonathan Blitz

How do I export the recordet received from an SQL query directly to an Excel
file?

--
Jonathan Blitz
AnyKey Limited
Israel

"When things seem bad
Don't worry and shout
Just count up the times
Things have worked themselves out."
 
Either

(1) write it to a table and then export the table, or

(2) create a querydef from the SQL statement, export the query, and then
delete the querydef, or

(3) use Automation to open an EXCEL file and write the recordset's contents
into a spreadsheet in that file.

Post back if you have questions about the options.
 
The only viable option is number 3.

When you say automation do you mean to open an Execl object from VBA and
write to it?

Are there methods for writing the data using the object?

--
Jonathan Blitz
AnyKey Limited
Israel

"When things seem bad
Don't worry and shout
Just count up the times
Things have worked themselves out."
 
It's not overly difficult to do; here's some generic code for doing that
part (looping through the recordsets isn't difficult either):

Dim xlsApp As Object, xlsWB As Object, xlsWS As Object, xlsRng As Object
Set xlsApp = CreateObject("Excel.Application")
Set xlsWB = xlsApp.Workbooks.Open("C:\FolderName\FileName.xls", , True)
Set xlsWS = xlsWB.Worksheets("WorkSheetName")
Set xlsRng = xlsWS.Range("A1")

' loop through your recordset and write each field's value into a cell in a
row
' this example doesn't show the changes you'd make to the Range object,
' but you can change its assignment or use .Offset method of the Range
object
' or other methods
xlsRng.Value = RecordsetName.Fields(fieldindexnumber)
'

Set xlsRng = Nothing
Set xlsWS = Nothing
xlsWB.Close True
Set xlsWB = Nothing
xlsApp.Quit
Set xlsApp = Nothing



My answers assumed that your original question referred to a recordset that
you're getting in VBA code, not to a stored query. If your "recordset" is a
stored query, then you can use the TransferSpreadsheet action
(DoCmd.TransferSpreadsheet) to export from a query to an EXCEL file.
 
Back
Top