Copy recordset data into multiple sheets to avoid problem of maximum rows limit in Excel VBA
- by Sam
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 rertrieved 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 spliting the data I want to ensure that the data for same product shoud 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 cotain records for product id 3 - with total records = 2.
How can I acheive this in vba?
If it is not possible, is there any alternative solution ?
Thanks in Advance !