Here's some code that might help. Only difference is in my example the sql
is driven from the rowsource of a listbox. You would use the sql driving
the form. I can't remember if when filtering the recordsource changes
appropriately (I usually just change the recordsource), if not you need to
create the sql from the recordsource and the current filter.
If you can't get it to work email me and I'll look again.
HTH,
Mark
RPT Software
http://www.rptsoftware.com
Private Sub cmdExportToExcel_Click()
'export current data in MainList to Excel
'uses whatever columns you have in the MainList listbox
'Note: you can use the excel export on the detail screen to get all columns
On Error GoTo Err_cmdExportToExcel
Dim sql As String
Dim filepath As String
sql = Me.MainList.RowSource
filepath = CurrentDBDir() & Me.lblHeading.Caption & "_Export.xls"
Call ExportQueryToExcel("qryExport", sql, filepath)
Exit_cmdExportToExcel:
Exit Sub
Err_cmdExportToExcel:
MsgBox Err.Description
Resume Exit_cmdExportToExcel
End Sub
Function CurrentDBDir() As String
Dim strDB As String, strCurrentDir As String
strDB = CurrentDb.Name
strCurrentDir = Left(strDB, Len(strDB) - Len(Dir(strDB)))
CurrentDBDir = strCurrentDir
End Function
Public Sub ExportQueryToExcel(QueryName As String, sql As String, filepath
As String)
On Error GoTo Err_ExportQueryToExcel
'exports the specified query to the specified filepath
'Note: if sql <> "" then populate query with sql and save first before
exporting
Dim dbs As Database
Dim dbQueryDef As DAO.QueryDef
If (sql <> "") Then
Set dbs = CurrentDb()
Set dbQueryDef = dbs.QueryDefs(QueryName)
dbQueryDef.sql = sql
dbQueryDef.Close
End If
DoCmd.OutputTo acOutputQuery, QueryName, acFormatXLS, filepath
MsgBox "Export File Created: " & filepath, , "Export"
Exit_ExportQueryToExcel:
Set dbQueryDef = Nothing
Set dbs = Nothing
Exit Sub
Err_ExportQueryToExcel:
MsgBox Err.Description
Resume Exit_ExportQueryToExcel
End Sub
