Loop Through Table or Query and Export to Excel

[jhabey]

Hello,
I am trying to export a table in access based on a unique field called Group_Name. Say my table has 100 records. 10 of those records belong to Group1, 10 belong to Group 2, and so on. What I want to do is export those groups individually to an excel file and have that file named somting like Group1_Premium Detail Report.

Here is the Code I have so far. I have a feeling I am close. When I run the code it does not seem to like strrsql2 :banghead:

Dim strsql As String
Dim strsql2 As String
Dim strfilename As String
Dim strpath As String
strpath = "C:\Users\Desktop\Home\"
strsql = "select distinct Group_name From Tbl_GrpName"
Set db = CurrentDb()
Set rs = db.OpenRecordset(strsql)

rs.MoveLast
rs.MoveFirst
Do Until rs.EOF

strsql2 = "Select Tbl_DetailRpt.* " _
& "from Tbl_DetailRpt " _
& "where [Tbl_DetailRpt].[Group_Name]=" & rs!Group_name

strfilename = [rs]![Group_name] & " Premium Detail Report"
DoCmd.OutputTo acOutputQuery, strsql2, , strpath & "REC01" & "_" & strfilename & ".xls", True, ""

DoCmd.TransferSpreadsheet acExport, , strsql2, strfilename, True
rs.MoveNext
strfilename = ""
strsql2 = ""
Loop
Exit Sub
End Sub


Thanks in advance for the input.

 

[pbaldy]

If the field is text, try

& "where [Tbl_DetailRpt].[Group_Name]='" & rs!Group_name & "'"

 

[jhabey]

Yes it is a text field. I changed the the line to what you mentioned (which makes senese) I know I gotta be close. I am getting a runtime error 3011L The ms database engine could not find the object 'Select tbl_detailrpt.* from the tbl_detailrpt where [tbl_detailrpt].[group_name] = "Group1" Make sure the object exists and that you spell its name and the path correctly.

Does it make sense what I am trying to do?
Thanks

 

[MarkK]

Maybe post your exact code again where you do this . . .

strsql2 = "Select Tbl_DetailRpt.* " _
& "from Tbl_DetailRpt " _
& "where [Tbl_DetailRpt].[Group_Name]=" & rs!Group_name

 

[pbaldy]

Oh, I don't think OutputTo works with SQL, just saved queries or tables.

 

[MarkK]

Oh yeah, that's it. It's looking for the name of an object, not the SQL.

 

[nanscombe]

Right. I have attached a demo of the following.

I started by writing the function tmpGroupName() that would allow me to pass a value into a query.

Public Function tmpGroupName(Optional ByVal tgnGroupName As Variant = "")
Static sVarGroupName As Variant

    If Len(tgnGroupName & vbNullString) > 0 Then sVarGroupName = tgnGroupName
    tmpGroupName = Nz(sVarGroupName)
End Function

If I pass a value into tmpGroupName() it remembers it for next time. If I pass no value in it just returns the value already stored in it.

debug.Print tmpGroupName("Test1")
Test1
debug.Print tmpGroupName()
Test1
debug.Print tmpGroupName("Test2")
Test2
debug.Print tmpGroupName()
Test2

I then created the query qryDetailRptForGroup which would pass back records based on the value passed to it by tmpGroupName().

SELECT Tbl_DetailRpt.*
FROM Tbl_DetailRpt
WHERE (((Tbl_DetailRpt.Group_Name)=tmpGroupName()));

I then set about creating the Function outputDetailRptByGroup() which would loop through all distinct values of field Group_Name in table Tbl_DetailRpt in turn and pass the values to tmpGroupName().

This means that query qryDetailRptForGroup will only contain records for the selected Group_Name.

Public Function outputDetailRptByGroup()
Dim rs As Recordset
Dim strPath As String
Dim strFileName As String

  strPath = "C:\Users\Desktop\Home\"

  Set rs = CurrentDb.OpenRecordset("SELECT DISTINCT [Group_Name] FROM [Tbl_DetailRpt] ORDER BY [Group_Name]", dbOpenDynaset)

  With rs
  
    .MoveFirst
    While Not .EOF
      tmpGroupName (![Group_Name])
      
      strFileName = ![Group_Name] & " Premium Detail Report"

      DoCmd.OpenForm "pfrmResults", , , , , acDialog, strPath & "REC01" & "_" & strFileName & ".xls"
'      DoCmd.TransferSpreadsheet acExport, , "qryDetailRptForGroup", strpath & "REC01" & "_" & strfilename & ".xls",, True
      .MoveNext
    Wend
    
  End With
  
  rs.Close
  Set rs = Nothing
End Function

I have simulated the output of the query results with the popup form pfrmResults.

Take a look and see if this does close to what you want.