Exporting results of a filtered query to excel (1 Viewer)

[jmv9430]

Hello all.

I've tried repeatedly to get an "Export to Excel" function to work. I have created a search form with various criteria that the user selects to filter the query results. My problem is that no matter how I code it (DoCmd.Transferspreadsheet, DoCmd.OutputTo) I can't seem to get it right. In all cases it exports the query without applying the filter. The search feature works great building up a filter string and displaying the results. However exporting these results is something completely different.

How do I export the RESULTS of my filtered query search? I have listed my current code below. (I have cut the stub code out for space)

If I am not clear on any point please ask, I will be glad to give more info.

Private Sub cmdExcelExport_Click()
Dim strWhere As String
Const strcStub = "SELECT tblEmployeeData.[Last Name] FROM.......>>>>
Const strcTail = "ORDER BY tblEmployeeData.[Last Name];"
Dim strSql As String

Dim strWhere As String
If Me.FilterOn Then
strWhere = "WHERE (" & Me.Filter & ") "
End If
strSql = strcStub & strWhere & strcTail
'Debug.Print strSql
CurrentDb.QueryDefs("qryRecordSearch").SQL = strSql
DoCmd.TransferSpreadsheet acExport, , "qryRecordSearch", "C:\TEAPDTexport.xls"

End Sub

Thanks!

 

[ted.martin]

Macros are wonderful things! Create a macro and export the query to an Excel file. If you need to do this in VB, then use the tools option to convert your macro to code and then strip this code into your code above.

 

[ted.martin]

The macro command is OutputTo

 

[jmv9430]

I have tried both OutputTo as well as transferspreadsheet, both export the results of my query without applying the filter that was builtup in the search form. So while both OutputTo and transferspreadsheet may work, I need assistance figuring out what I'm missing in my code that prevents the filter from applying.