Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unable to Insert Worksheet/Move and copy worksheet Excel 2003 | Excel Worksheet Functions | |||
Upload multiple text files into 1 excel worksheet + put the filename as the first column in the worksheet | Excel Worksheet Functions | |||
Linking Formulas, Worksheet to Worksheet - Excel 2003 | Excel Discussion (Misc queries) | |||
excel 97: copy and paste values from one worksheet to another worksheet | Excel Programming | |||
Attaching a JET database to an Excel Worksheet OR storing large binary data in a worksheet | Excel Programming |