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!
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!