vba exporting query to excel based on certain field

[VBANEWBIE]

Hello All,

I'm stuck on this VBA code and cant seem to see what's wrong.

running... access 2016, excel 2016

objective...
have a query named “QryDetailParticipants401”
contains four fields, cycler, RptName, Company, LastName.
I would like to export the records into a spreadsheet however, a new spreadsheet but be created every time the “cycler” field changes.
I have done similar stuff like this but it was creating pdf, not excel.
I’m fairly new to VBA so any help would be greatly appreciated…
Here is the current code… and thanks again

 Private Sub Create_401_Spreadsheet_Click()
 On Error GoTo OutputReports_Click_Err
 Dim QueryName As String
 Dim OutputFolder As String
 Dim rs As DAO.Recordset
 ReportName = "QryDetailParticipants401"
 OutputFolder = "C:\Users\me\Documents\CENREQTempFolder\"
 Set rs = CurrentDb.OpenRecordset("SELECT Cycler,  RptName, FROM QryDetailParticipants401")
 Do While Not rs.EOF
     DoCmd.OpenQuery QueryName, acViewNormal, "[Cycler] = '" & rs!Cycler & "'", acHidden
     DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, OutputFolder & rs!RptName & "- 2.XLS", True
     DoCmd.Close acReport, QueryName
     rs.MoveNext
 Loop
 rs.Close
 OutputReports_Click_Exit:
 MsgBox "401(k) Census Spreadsheet Produced"
     Exit Sub
 OutputReports_Click_Err:
     MsgBox Error$
     Resume OutputReports_Click_Exit
 End Sub

 

[VBANEWBIE]

sorry, I'm not following
where and how would I do that?

 

[VBANEWBIE]

I changed...
ReportName = "QryDetailParticipants401"

to...
QueryName = "QryDetailParticipants401"

now im getting a "complie error"
Wrong number of arguments or invalid property assignment
on the line that states...

DoCmd.OpenQuery QueryName, acViewNormal, "[Cycler] = '" & rs!Cycler & "'", acHidden

 

[VBANEWBIE]

Uncle Gizmo, thank you for your time.