How can i write the code such that when it reach filling the end of the
row of an excel worksheet, it will continue filling the other sheet let
say sheet2.



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

It really depends on what you are doing and how you are doing it, but

if rw = 65536 then
set sh = sh.Next
rw = 1
End
sh.Cells(rw,1) = "some value"

However, there is no built in method to fill multiple worksheets with a
single command such as writing an ADO recordset or reading a text file
(unless one was added in xl2002/2003 and I don't recall that they have).

--
Regards,
Tom Ogilvy


"Nats Buls" wrote in message
...
How can i write the code such that when it reach filling the end of the
row of an excel worksheet, it will continue filling the other sheet let
say sheet2.



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nats Buls wrote:
How can i write the code such that when it reach filling the end of
the
row of an excel worksheet, it will continue filling the other sheet
let
say sheet2.

Here's a suggestion (thanks TK). Using Excel's CopyFromRecordset on a
recordset with more records than the 65536 maximum worksheet rows does
not cause the method to fail. Rather, the cursor is merely moved e.g.
to record 65537. Therefore, you could do something like this:

Sub test()
Dim rs As Object
Set rs = CreateObject("ADOR.Recordset")
rs.Open _
"SELECT * FROM 100K_row_table;", _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Tempo\New_Jet_DB.mdb"

Dim Counter As Long
With Workbooks("MyWorkbook.xls")
Do While Not rs.EOF
Counter = Counter + 1
.Worksheets(Counter).Range("A1") _
.CopyFromRecordset rs
Loop
End With
End Sub

Jamie.

--