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