The following code will export the results of a specific SQL statement to an Excel file:
Dim sSQL As String
Dim qdf As QueryDef
sSQL = "SELECT qryMain.* FROM qryMain WHERE (((qryMain.date_shipped)=[Forms]![frmSearch]![txtDate])) "
Set qdf = CurrentDb.CreateQueryDef("qryTemp", sSQL)
DoCmd.OutputTo acQuery, "qryTemp", "MicrosoftExcel(*.xls)", "", True, ""
Set qdf = Nothing
'delete the temp query
CurrentDb.QueryDefs.Delete "qryTemp"
What this code does is create a temporary query based on the SQL statements in the sSQL variable. It then exports this query, then deletes it (it's temporary, after all). Simply substitute your own SQL statements in the sSQL variable. The "output object" method (not DOCMD.TRANSFERSPREADSHEET method) is used.
Note: errors can result if, due to an error in the code, the qryTemp is not deleted and Access attempts to create it again the next time when it already exists. To fix this, go to my web page which explains how to check if a query already exists.
Another handy use for this is to export the current form's RECORDSOURCE to Excel format (great for seeing the results of a search form, say, and then deciding--hey, I'd like to export all of this to Excel). To do this, use the same code as above, but change the sSQL= statement to read:
sSQL = Me.RecordSourceLike the above code, it places the values derived from the RECORDSOURCE of the form into a temporary query, then it exports this query directly.