Send query data to excel using ADO

[billy.rogers]

I'm trying to build a worksheet with several tabs that each show the
result of a query. This code works when I use a table name, but does
not work when I use a query. Do I need to code this differently for a
query?


Public Sub AcquisitionReport()


Set xlApp = New Excel.Application
Set appworkbook = xlApp.Workbooks.Add
Set appWorkSheet1 = appworkbook.Worksheets(1)
appworkbook.Worksheets.Add
appworkbook.Worksheets.Add
xlApp.Visible = True

appworkbook.Worksheets(1).Name = "AMDS"
appworkbook.Worksheets(2).Name = "IMA"
appworkbook.Worksheets(3).Name = "QCPS"
appworkbook.Worksheets(4).Name = "TVP"
appworkbook.Worksheets(5).Name = "USMS"


Dim Row As Integer


Dim rst1 As ADODB.Recordset
Set rst1 = New ADODB.Recordset


Dim fld As ADODB.Field
Dim cmd As New ADODB.Command


' *********************** this works for tables but not
queries.********************************
rst1.Open "[TableName]", CurrentProject.Connection, adOpenKeyset

Column = 1
Row = 1

For Each fld In rst1.Fields

xlApp.Workbooks(1).Worksheets(1).Cells(Row, Column).Value =
fld.Name
Column = Column + 1

Next fld

xlApp.Workbooks(1).Worksheets(1).Cells(2, 1).CopyFromRecordset rst1




End Sub

 

[Yanick]

In the rst1.open statment, try to replace dbOpenKeyset by dbOpenDynaset.

To export to Excel you can use docmd.TransferSpreadsheet too. Less complex
to implement and support.