sub open_query ()
Dim db As DAO.Database
Dim rs, rs2, rs3 As DAO.Recordset
Dim qry, dbqry, dbqry2 As DAO.QueryDef
Dim xlapp as excel.application
Set db = Access.CurrentDb
Set qry = db.QueryDefs("Query1")
Set rs = qry.OpenRecordset
call CopyRecordSet(rs)
end sub
Sub CopyRecordSet(InRecSet As Recordset)
Dim xlapp As Excel.Application, xlNewBook As Excel.Workbook, xlDestSheet, xlDestSheet2 As Excel.Worksheet
Dim fld As DAO.Field
Dim i As Integer
'open excel
Set xlapp = New Excel.Application
xlapp.Visible = True
'add new sheets for each recordset
Set xlNewBook = xlapp.Workbooks.Add
Set xlDestSheet = xlNewBook.Worksheets.Add
'copy query into excel sheet
i = 1
For Each fld In InRecSet.Fields
xlDestSheet.Cells(1, i).Value = fld.Name
i = i + 1
Next fld
xlDestSheet.Range("A2").CopyFromRecordset InRecSet
xlDestSheet.Name = ("Data")
End Sub