Excel Worksheet and VBA

[Guest]

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.

 

[Guest]

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

 

[NickHK]

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