vba exporting query to excel based on certain field (1 Viewer)

VBANEWBIE

Registered User.
Local time
Today, 05:49
Joined
Oct 17, 2016
Messages
17
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

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
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 09:49
Joined
Jul 9, 2003
Messages
16,245
You haven't given the string variable "QueryName" a query name to pass in...
 

VBANEWBIE

Registered User.
Local time
Today, 05:49
Joined
Oct 17, 2016
Messages
17
sorry, I'm not following
where and how would I do that?
 

VBANEWBIE

Registered User.
Local time
Today, 05:49
Joined
Oct 17, 2016
Messages
17
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
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 09:49
Joined
Jul 9, 2003
Messages
16,245
sorry, I'm not following
where and how would I do that?


In the Subroutine you posted, you have a string variable "Dim QueryName As String" but you have not assigned a string to it in other words you haven't given it the name of the query you wanted to run Here:- "DoCmd.OpenQuery QueryName, ........"
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 09:49
Joined
Jul 9, 2003
Messages
16,245
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

With your changes you are now trying to close a query, but as a report....

So now Here:-
DoCmd.Close acReport, QueryName

So maybe it should be something like:-

DoCmd.Close acQuery, QueryName....
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 09:49
Joined
Jul 9, 2003
Messages
16,245
But really I think you are going about it the wrong way. You have copied a function from somewhere and hacking it round with hardly any knowledge of what you are doing, and then asking someone to fix it. I don't think we're going to get very far like this...
 
Last edited:

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 09:49
Joined
Jul 9, 2003
Messages
16,245
Well, a better approach would be to cut it back to the basics.

First, get the Recordset to return rptName to a message box.

MsgBox " >>> " & rs.rptName

Sent from my SM-G925F using Tapatalk
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 09:49
Joined
Jul 9, 2003
Messages
16,245
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

MsgBox " >>> " & rs.rptName

     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

Try that, I did it on my phone so I may not have it quite right.

Sent from my SM-G925F using Tapatalk
 
Last edited:

Users who are viewing this thread

Top Bottom