Filter output to excel

[Sammie]

I have a command button on a subform which sends the subform's query to
excel. I need to filter the query to export only the records on the subform
related to the current main form record. How do I do it? My command
button's code is:

DoCmd.RunCommand acCmdOutputToExcel

 

[GBA]

generically; first you need to create a recordset with the data you want; and
then you must export that recordset

create a query that results always in the data you want to export;

modify your command to instead export this new query

 

[Mark Andrews]

Just pass in the sql and a queryname needed to create the excel file to this
function.
You can have one generic "qryExport" query that just gets overwritten
depending on conditions.


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

HTH,
Mark
RPT Software
http://www.rptsoftware.com