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
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
Code:
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