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)
qdfTemp.Close
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, _
      strQDF,”C:\MyFolderName\MyFileName.xls”

dbs.QueryDefs.Delete strQDF
dbs.Close
Set dbs = Nothing

By Ken Snell