Parameterized queries + DoCmd.TransferSpreadsheet acExport

I need to export a large amount of complex data to an excel spreadsheet. To facilitate this, I have created several large queries. Now I’m looking to filter these queries based on date range and some other tidbits of information, but I am unsure how to do so.

DoCmd.TransferSpreadsheet acExport, , “Overview_Agents_Query”, xlsxPath, True, “Agents”

This is how I am exporting the query to an Excel spreadsheet currently. I did not realize when forming these queries that I could not pass a Recordset, only a string name of a Table or Query. My second attempt looks like:

StrAgents = "SELECT * FROM Overview_Agents_Query" 

# Build a WHERE clause here

Set qdfAgents = CurrentDb.CreateQueryDef("Temp_Agents_Query", StrAgents)

DoCmd.TransferSpreadsheet acExport, , qdfAgents.Name, xlsxPath, True, "Agents"

CurrentDb.QueryDefs.Delete qdfAgents.Name

Which works for the two simple queries, but is impractical for some of the more complicated ones due to the use of nested queries, joins, and unions. I would really like to avoid hard-coding large blocks of SQL report strings directly into my VBA code, although I suppose that’s better than nothing if no solution exists.

Any ideas on how I can accomplish this?

Answer

You can modify the queries to allow for inserting a where clause:

' SQL of query:
' Select * From SomeTable Where {0}

Dim SQL As String
Dim Filter As String

Filter = "(SomeField = '" & YourSearchString & "')"

Set qd = CurrentDb.QueryDefs("YourQuery")
SQL = qd.SQL
qd.SQL = Replace(SQL, "{0}", Filter)
qd.Close

DoCmd.TransferSpreadsheet acExport, , qd.Name, xlsxPath, True, "Agents"

' Restore original SQL.
Set qd = CurrentDb.QueryDefs("YourQuery")
qd.SQL = SQL
qd.Close