Copy recordset data into multiple sheets to avoid problem of maximum rows limit in Excel VBA

Question

I am developing reporting application in Excel/vba 2003. VBA code sends search query to database and gets the data through recordset. It will then be copied to one of excel sheet. The retrieved data looks like as shown below.

ProductID--------|---DateProcessed---------|----State-----  

1................|.. 1/1/2010..............|.....Picked Up  
1................|.. 1/1/2010..............|.....Forward To Approver   
1................|.. 1/2/2010..............|.....Approver Picked Up             
1................|.. 1/3/2010..............|.....Approval Completed     
2................|.. 1/1/2010..............|.....Picked Up  
3................|.. 1/2/2010..............|.....Picked Up  
3................|.. 1/2/2010..............|.....Forward To Approver   

The problem is data retrieved from search query is so huge that it goes above the excel row limit (65536 rows in excel 2003). So I want to split this data into two excel sheets. While splitting the data I want to ensure that the data for same product should remain in one sheet.

For example, if the last record in the above result set is 65537th record then I also want to move all records for product 3 into new sheet.

So sheet1 will contain records for product id 1 and 2 with total records = 65534. Sheet 2 will contain records for product id 3 - with total records = 2.

How can I achieve this in vba?

If it is not possible, is there any alternative solution ?

Thanks in Advance !

Answer 1

Since you did not provide any code on how you are accessing your database, I can give you only a general outline.

First, send a query to the database like this:

"SELECT COUNT(*),ProductID FROM YourTable GROUP BY ProductID order by ProductID"

From the result you can easily calculate ProductID intervals with less than 65536 records, just add the count up until you reach this limit.

Finally, you split your original query into many queries (one per data sheet), and add a WHERE condition like this:

... WHERE ProductID IS BETWEEN " + LowerIDRange + " AND " + HigherIDRange

LowerIdRange and HigherIDRange have to be the interval limits calculated from the first query.

Answer 2

Move to Excel 2007 or 2010, which have a limit of 250.000 rows I think. Or better: see if you can pre-agregate you data.
My prefered suggestion here would be to make a pivot table summarizing your data. Then by double clicking a cell, the user could get a matching subset of detail records into a new sheet.