Export Sql Query To Excel File

if you want to export the result of query to excel file.

Here is the code.

Sub export_access_query_excel()
DoCmd.SetWarnings (False)

Dim temp_qry As DAO.QueryDef
Dim temp_table_name As String
Dim exportfile As String
exportfile = "C:\Users\ashishkoul\Desktop\learn\rep_name_a.xlsx"
temp_table_name = Format(Now(), "dd_mmm_yyyy_hh_ss")
' check if the any excel file aready exists with same name
If Dir(exportfile) <> "" Then
MsgBox "File already exists"
Exit Sub
End If
' create a new temp table from query and then we will export the table to excel and later we will delte the temp table
DoCmd.RunSQL "SELECT * INTO " & temp_table_name & " FROM sales_detail WHERE [rep name] = 'a'"
DoCmd.TransferSpreadsheet acExport, , temp_table_name, exportfile, True
On Error Resume Next
'close the table if open
DoCmd.Close acTable, temp_table_name, acSaveYes
On Error Resume Next
' delete the temp table
CurrentDb.TableDefs.Delete temp_table_name
DoCmd.SetWarnings (True)
End Sub


Download Access Database