Export to Excel, multiple files by filter

[eortiz]

Hi

I have a macro set it up to export a report and creates a pdf for each value filtered. However, I tried to do the same to export a Query to Excel, but it is not working.

This is the macro that works (exporting Report to pdf)
Option Compare Database
Sub Print_by_AMP()



Dim MyPath As String
Dim rs As Recordset

MyPath = "H:\test"


Set rs = CurrentDb.QueryDefs("Q_AMP_ID").OpenRecordset

rs.MoveFirst
While Not rs.EOF
DoCmd.OpenReport "Rpt_AMP_Summary_Report", acViewPreview, , "AMP_ID = " & rs("AMP_ID") ' & "'"
DoCmd.OutputTo acOutputReport, "", acFormatPDF, MyPath & rs("AMP_ID") & "_AMP_Perform_Summ_2018Q2YTD.pdf", False
DoCmd.Close acReport, "Rpt_AMP_Summary_Report"

rs.MoveNext
Wend

End Sub



This is the macro that does not work (exporting query yo Excel). The error is: Wrong number of arguments or invalid property assignment.

Option Compare Database
Sub Print_by_AMP2()

Dim MyPath As String
Dim rs As Recordset

MyPath = "H:\test"


Set rs = CurrentDb.QueryDefs("Q_AMP_ID").OpenRecordset

rs.MoveFirst
While Not rs.EOF
DoCmd.OpenQuery "Q_AMP_RUNNING_BALANCE", acViewPreview, , "AMP_ID =" & rs("AMP_ID") ' & "'"
DoCmd.OutputTo acOutputQuery, , acFormatXLSX, MyPath & rs("AMP_ID") & "_AMP_Perform_Summ_test.xlsx", False
DoCmd.Close acQuery, "Q_AMP_RUNNING_BALANCE"

rs.MoveNext
Wend

End Sub

[pbaldy]

You can't assume that OpenQuery has the same arguments available as OpenReport; it doesn't. One option is adding a criteria to the query that points to a textbox on a form. Within your loop, populate that textbox with the value from the recordset before exporting.

[ijaz8883]

Mr. eortiz


Try this method
https://www.accessforums.net/showthr...t=75234&page=3