Export SQL query Result to Excel using DAO

If you want to export the result of query to Excel .

Here is the code-


Option Compare Database
Sub export_access_query_data_to_excel_using_DAO()
Dim xlapp As New Excel.Application
Dim wbk As Excel.Workbook
Dim WKS As Excel.Worksheet
Dim S As String
Dim rst As DAO.Recordset
Dim fld As Field
Dim i As Integer
i = 1
xlapp.Visible = True
xlapp.ScreenUpdating = False
xlapp.DisplayAlerts = False
Set wbk = xlapp.Workbooks.Add
Set WKS = wbk.Worksheets.Add
WKS.Name = "Data"
'**************** delete empty sheets"
wbk.Sheets("Sheet1").Delete
wbk.Sheets("Sheet2").Delete
wbk.Sheets("Sheet3").Delete
'*********************** copy header of query**************************
Set rst = CurrentDb.OpenRecordset("Select * from sales_detail where [Rep Name] ='a'")
For Each fld In rst.Fields
WKS.Cells(1, i).Value = fld.Name
WKS.Cells(1, i).Interior.Color = vbCyan
WKS.Cells(1, i).Font.Bold = True
i = i + 1
Next fld
rst.MoveFirst
'************************ copy the data from access to excel
WKS.Range("A2").CopyFromRecordset rst
xlapp.ScreenUpdating = True
xlapp.DisplayAlerts = True
Set xlapp = Nothing
Set wbk = Nothing
Set rst = Nothing
End Sub

Download Database