Excel Worksheet and VBA
I have this excel template that has vb code that looks at an access db query,
and writes the contents of that query in the excel sheet. I want to know if there is a way, depending on the count of records, to split the data in 2 worksheets for instances, if the query brings back 1000 records, I would like the first 500 to be shown on the first worksheet and the second one on the second worksheet. Im using an array function currently, but all teh data is on the first worksheet and I have to manually split it. |
Excel Worksheet and VBA
Yes there is a way.
Instead of using Range.CopyFromRecordset feature of the Range object what you need to do is to write out the recordset one row at a time. Dim ws as worksheet Dim lNumRecordsWritten&,lRowNumber& lNumRecordsWritten = 0:lRowNumber=1 Do while not rs.eof if wNumRecordsWritten <500 then Set ws=Worksheets(1) else Set ws=Worksheets(2) Endif lNumRecordsWritten=wNumRecordsWritten+1 ws.Cells(lRowNumber,1).value = rs(0).value ws.Cells(lRowNumber,2).value = rs(1).value 'and so on 'I normally store the recordset values in an array and then 'Write the whole array in the row. This array idea can be extended 'also to the entire rectangular area. The array business is useful 'if you have hundreds of rows to write since it is much faster. lRowNumber=lRowNumber+1 rs.MoveNext Loop "Justin" wrote: I have this excel template that has vb code that looks at an access db query, and writes the contents of that query in the excel sheet. I want to know if there is a way, depending on the count of records, to split the data in 2 worksheets for instances, if the query brings back 1000 records, I would like the first 500 to be shown on the first worksheet and the second one on the second worksheet. Im using an array function currently, but all teh data is on the first worksheet and I have to manually split it. |
Excel Worksheet and VBA
Justin,
Depends how you are getting your data. If with ADO, you can use the recordset to move to the 2nd sheet after 500 records, or execute a query first to Count how many records will be returned. If with a querytable, then process after you have the data. NickHK "Justin" ... I have this excel template that has vb code that looks at an access db query, and writes the contents of that query in the excel sheet. I want to know if there is a way, depending on the count of records, to split the data in 2 worksheets for instances, if the query brings back 1000 records, I would like the first 500 to be shown on the first worksheet and the second one on the second worksheet. Im using an array function currently, but all teh data is on the first worksheet and I have to manually split it. |
All times are GMT +1. The time now is 08:31 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com