I've tried this code. It is still giving me the first 1000 rows. I think this
code, basically, is doing just that, loading the first 1000 rows only?? Am I
wrong? How does it load the SECOND 1000 rows?

"keepITcool" wrote:


I've dispensed with the pagesize..
also..
Do you really need the Keyset? adopenForwardonly (0)
seems enough and is "lighter"


on a local test querying an excel file..this works for me..
(I realise OWC doesnt have the 65k row limit)

cn.Cursorlocation = 3
cn.Open

Set rs = CreateObject("adodb.recordset")
rs.Open "Select * from [Sheet1$]", cn, 0 'Static Forward cursor

nStep = 1000
nMax = rs.RecordCount
If nMax 65535 Then nMax = 65535

For n = 1 To nMax Step nStep
ActiveSheet.Cells(n, 1).CopyFromRecordset rs, nStep
Next

rs.Close
cn.Close




keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


?B?cndlaWRl?= wrote:

Hi, all,

I am trying to load a spreadsheet (OWC) on my ASP page directly from
the RecordSet.

However, since the returned recordset can be huge, I will only load
the spreadsheet with first 1000 rows, if users choose to see the next
1000 rows, I will load the next 1000 rows - pretty much treating the
spreadsheet as a list box but with Excel functions.

I do by something like this:

rs.CursorLocation = 3 'adUseClient
rs.CursorType = 1 'adOpenKeyset
rs.LockType = 3 'adLockOptimistic
rs.PageSize = 1000
rs.Open sSQL, Cnn
SS1.ActiveSheet.Cells.copyFromRecordset rs,1000
And this has been successful. But when I try to load the next 1000
rows, it still displays the first 1000 rows. I do this by:

rs.AbsolutePage = 2
SS1.ActiveSheet.UsedRange.ClearContents
SS1.ActiveSheet.Cells.copyFromRecordset rs,1000

I check the recordset before the "copyFromRecordset" method, and the
recordset is at the correct position. But the "copyFromRecordset"
seems to ignore it, and load the spreadsheet from the BOF of the
recordset. Anyone knows about this? Thanks.