Examples for Exporting Access table or query to EXCEL Workbook Files Part 1

Create and Export a
Parameter Query to EXCEL file via TransferSpreadsheet (VBA)

Generic code to generate “on the fly” a query that uses one or more controls on
an open form as parameters, and then export that query to an EXCEL file. This
example concatenates the parameter values into the generated SQL statement and
then saves the query so that it can be exported. The query then is deleted after
the export is completed.


Dim dbs As DAO.Database
Dim qdfTemp As DAO.QueryDef
Dim strSQL As String, strQDF As String
Set dbs = CurrentDb

‘ Replace NameOfTableOrQuery with the real name of the table or query,
‘ replace NameOfForm with the real name of the form, and replace
ADateControlOnForm and AnotherDateControlOnForm with the real names

strSQL = “SELECT NameOfTableOrQuery.* FROM NameOfTableOrQuery ” & _   
“WHERE NameOfTableOrQuery.FieldName >= ” & _     
Format(Forms!NameOfForm!ADateControlOnForm.Value,”\#mm\/dd\/yyyy\#”) & _     
” And NameOfTableOrQuery.FieldName <=” & _     
Format(Forms!NameOfForm!AnotherDateControlOnForm.Value,”\#mm\/dd\/yyyy\#”) &

‘ of the controls on that form

strQDF = “_TempQuery_”
Set qdfTemp = dbs.CreateQueryDef(strQDF, strSQL)
Set qdfTemp = Nothing

‘ Replace C:\MyFolderName\MyFileName.xls with the real path and
filename for the
‘ EXCEL file that is to contain the exported data

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _

dbs.QueryDefs.Delete strQDF
Set dbs = Nothing

By Ken Snell 

Leave a Reply