Posted to microsoft.public.excel.programming
|
|
CopyFromRecordset with large amount of data
Very excited to receive this reply so fast. I'll need to try it first thing
Monday morning, since I'll be off until then. Thank you!!!
"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.
|