Exporting Recordsets to .xls

[nanohurtz]

Hi,

I am trying to write a SubRoutine/SQL statement that will group data by zipcode and export each recordset into individual spread sheets based on those groupings. (i.e. 10472.xls 10888.xls..). I've written the SQL to group all Zipcodes and output to a recordset (rst). The second query uses the newly created (rst) to filter out the groups from the table. The problem I'm having is generating the individual .xls output for every group (rstb) collected. Can anyone offer suggestions?

The DoCmd and Recordsets for some reason don't mix and my previous attempts at nested OpenRecordsets failed miserably. Perhaps my approach was wrong, Can anyone offer suggestions?

Private Sub Zippo Out

Set dbs= Current Db
strSQL = "SELECT tbl_data.zipcode FROM tbl_data GROUP BY tbl_data.zipcode;"
strSQLb = "SELECT tbl_data.zipcode, _
tbl_data.other FROM tbl_data _
WHERE (((tbl_data.zipcode)=" & Uzip & &quot);"

Set rst = dbs.OpenRecordset(strSQL)
rst.MoveLast, rst.MoveFirst
varRecords = rst.GetRows(rst.RecordCount)

For nRecords = 0 to UBound(varRecords, 2)
Uzip = varRecords(0, nRecords)

Set rstb = dbs.OpenRecordset(strSQLb)
<-- MISSING: Export Recordset to .xls -->

Next nRecords

rst.Close
Set dbs = Nothing
End Sub

End Sub

-SubCon

 

[nanohurtz]

forgot to mention, this is Access 97

 

[nanohurtz]

Im wondering if an array would work. Problem is that it's limited in the number of RecordSets it could hold