Send query data to excel using ADO

  • Thread starter Thread starter billy.rogers
  • Start date Start date
B

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