Export to Excel

[trackstar2786]

Brand new, so please excuse my inexperience. I'm trying to do two things.
1) Using the Code below, I'm attempting to create a new excel sheet that isolate each account number and exports that account's summary to an excel sheet. I'm currently stuck either in my SQL or vba statement how to have it be dynamic and move down the list of account numbers. Otherwise the naming convention for excel works fine.
2) There is a detail tab that I would need to create/do the same extraction (except multiple lines) that has the same corresponding account number and place that detail in a second tab in the excel sheet that was created in the first portion.

Few issues when I run the below code:
1) It pulls all the accounts in the summary tab
2) the files save in my documents rather than the designated path.
3) I still need to do a breakout of a detail tab using the same corresponding account number and create/transfer it to a second sheet in the workbook

Private Sub BreakoutButton()

Dim qdf As DAO.QueryDef
     
For Each qdf In CurrentDb.QueryDefs
    If qdf.Name = "Commission_Statement" Then
        CurrentDb.QueryDefs.Delete "Commission_Statement"
         Exit For
      End If
    Next

Dim rs As DAO.Recordset
Dim qdfnew As DAO.QueryDef
Dim strSQL As String
Dim Path As String

'Path to the folder for the file to export to
Path = "F:\Commission Statement\Test Output"
'"L:\AIDC PM\Activation Tracking\Carrier Commission Payment Statement\Commission Statement-Kevin"

With CurrentDb
    Set rs = .OpenRecordset("ReportBreakout")
'Replace with name of the actual Account Table

rs.MoveFirst
'For each Account in the table
Do Until rs.EOF
strSQL = "Select Account, Reseller, ATTWireless, ATTWireline, Comcast, DataXoom, Spectrum, TMobile, TWC, VerizonWireless, VerizonWireline, Total  FROM ReportBreakout WHERE Account=rs.account"
'NEED A VARIABLE or DYNAMIC WHERE CLAUSE

'createquerydef command line follows
Set qdfnew = .CreateQueryDef("Commission_Statement", strSQL)
DoEvents
'Based on exporting file for previous month on the agreed upon 12th day
'DoCmd.TransferSpreadsheet acOutputQuery, "Commission_Statement", acFormatXLS, rs!Account & "_" & rs!Reseller & " " & Format(Date - 16, "mmmm yyyy") & ".xls", False, "Sheet1"
'DoCmd.TransferSpreadsheet acOutputQuery, "Commission_Statement", acFormatXLS, rs!Account & "_" & rs!Reseller & " " & Format(Date - 16, "mmmm yyyy") & ".xls", False, "Sheet1"
DoCmd.OutputTo acOutputQuery, "Commission_Statement", acFormatXLS, rs!Account & "_" & rs!Reseller & " " & Format(Date - 16, "mmmm yyyy") & ".xls", False
'DoCmd.OutputTo acOutputQuery, "Commission_Statement", acFormatXLS, rs!Account & "_" & rs!Reseller & " " & Format(Date - 16, "mmmm yyyy") & ".xls", False, "Sheet2"
            'export to a specific tab


'delete the temporay query
DoCmd.DeleteObject acQuery, "Commission_Statement"
Set qdfnew = Nothing
rs.MoveNext
Loop

rs.Close
Set rs = Nothing

End With
End Sub

 

[pbaldy]

This line would need to change. Presuming account is numeric:

strSQL = "Select Account, Reseller, ATTWireless, ATTWireline, Comcast, DataXoom, Spectrum, TMobile, TWC, VerizonWireless, VerizonWireline, Total FROM ReportBreakout WHERE Account=" & rs.account

You aren't using the path variable you created, you're just creating a file name. You'd need to concatenate them together.

 

[Cronk]

Paul, small typo in copy/paste
...... WHERE Account=" & rs!account

 

[pbaldy]

Good catch, I just copied and added the concatenation. Didn't notice that.

 

[Cronk]

Just heading off a come back from OP that your solution didn't work. Let's hope Account is a numeric field.

 

[trackstar2786]

The account number is a text but that is something that i can convert to a numeric field.

After I made that change it worked. Thank you!!

Now I have a detail transaction tab that I'll need to do the same breakout for, but there will be multiple transaction lines by account number. I'll need to pull all of one account and then place that into a "sheet2" called "Detail" in the same excel files i created above with the respective account number.

 

[pbaldy]

I don't know if that can be done with TransferSpreadsheet. I'd use Excel automation. Daniel has info here:

https://www.devhut.net/2016/07/07/vba-automating-excel/

 

[trackstar2786]

If I adjust the corrected VBA that I'm using for the Commission_Summary and point the VBA to run a breakout by account number my Detail query, it appears when running/exporting 1 account at a time (which I want) , it's going line by line vs moving to the next account group.

Example. if Account '123456' has 10 lines of detail, it's running the vba on account '123456' 10 times vs running it once, and then moving on to the next group, say 789101. So if i have 1000's of lines of transactions this will take some time....

Is there anyway to run by an account number vs running it by line?

 

[pbaldy]

The recordset you loop should return only what you want to loop, in this case one instance of each account number. One possibility:

Set rs = .OpenRecordset("SELECT DISTINCT Account FROM ReportBreakout")

 

[trackstar2786]

I made the change to the rs = .OpenRecordset("SELECT DISTINCT Account FROM Detail Breakoutout") but now I get a run time error '3265' 'Item not found in this collection.

 

[pbaldy]

Well, if there are inadvisable spaces in the table/query name, you'd have to bracket it.

 

[trackstar2786]

Sorry, i typed that too fast...

Set rs = .OpenRecordset("SELECT DISTINCT Account FROM DetailBreakout") is the direct copy/paste

This is the only thing i changed prior to it running line by line just fine. Which I guess i could do, but it's a waste since it took about a hour to run 6 accounts worth of detail.

 

[pbaldy]

Oh, later in the code you refer to another field (maybe others):

rs!Reseller

so that field would have to be included, and you'd get distinct combinations.

Set rs = .OpenRecordset("SELECT DISTINCT Account, Reseller FROM DetailBreakout")

 

[trackstar2786]

Thank you all for you help. I ended up going with two modules. One that runs the access query and exports to a new excel file and another module that runs a query and transfers it to a new tab in the first spreadsheet with the same corresponding file name.

Just have some minor changes I have to do/check on as I validate the reports.

 

[pbaldy]

Glad you got it working, post back if you get stuck.