Access VBA Export table to excel

Access VBA Export table to excel

Access VBA to export a table to excel.  CreateQueryDef creates a temporary query to SELECT the data from the table and Docmd.TransferSpreadsheet exports the data to Excel.  Update xlFileSaveName with the path for where you want the Excel to save to and update the SELECT query “SQLMasterPage” with your table name.

Dim db As DAO.Database
Set db = CurrentDb
 On Error Resume Next
    db.QueryDefs.Delete "ExportTempQuery"
 On Error GoTo 0
 xlFileSaveName = "Z:\Dylan\export.xls"
 SQLMasterPage = "SELECT Table1.* FROM Table1"
 Set MasterPage = db.CreateQueryDef("ExportTempQuery", SQLMasterPage)
 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, "ExportTempQuery", xlFileSaveName, True