Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
CopyFromRecordset with large amount of data
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
CopyFromRecordset with large amount of data
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. |
#3
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
CopyFromRecordset with large amount of data
keepITcool wrote ...
I realise OWC doesnt have the 65k row limit Does it required? The OP is clearing the 1000 rows each time, so they will never hit the max rows limit in Excel. 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 I'm not sure this is what the OP wanted. FWIW, for me the OP's code worked as expected i.e. for each page in the recordset, the sheet is cleared and the next 1000 rows appears in its place from the current bookmark rather than BOF. I was using a Jet ..mdb with the OLEDB provider for Jet 4.0 (i.e. fully supports bookmarks) on my local machine (i.e. no ASP). Jamie. -- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
CopyFromRecordset with large amount of data
again correct.
Jamie? offtopic... the other day tou mentioned something re protected mdb's and xls insert.. is following what you need? Sub MakeFromLocked() Dim oCN As New ADODB.Connection If Dir("d:\fromlocked.xls") < "" Then Kill "d:\fromlocked.xls" oCN.Provider = "Microsoft.Jet.OLEDB.4.0" oCN.Properties("Extended Properties") = "Excel 8.0;Header=Yes;" oCN.Properties("Jet OLEDB:System database") = "D:\xlSupport.mdw" oCN.Properties("Data Source") = "d:\fromlocked.xls" oCN.Open UserID:="keepITcool", Password:="cia" oCN.Execute "Select * INTO Dump FROM [MS Access;Database=d: \locked.mdb;PWD=nsa].[Table1]" oCN.Close Set oCN = Nothing End Sub keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool (Jamie Collins) wrote: keepITcool wrote ... I realise OWC doesnt have the 65k row limit Does it required? The OP is clearing the 1000 rows each time, so they will never hit the max rows limit in Excel. 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 I'm not sure this is what the OP wanted. FWIW, for me the OP's code worked as expected i.e. for each page in the recordset, the sheet is cleared and the next 1000 rows appears in its place from the current bookmark rather than BOF. I was using a Jet .mdb with the OLEDB provider for Jet 4.0 (i.e. fully supports bookmarks) on my local machine (i.e. no ASP). Jamie. -- |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
CopyFromRecordset with large amount of data
Back on topic...=)
I did load an activesheet with about 280,000 rows, it's slow but it works. However, when I need to leave this page (ASP page), it took forever and ever..... That's why I am trying to load partially, with 1000 rows at a time, which speed is acceptable. But as you've mentioned, my code SHOULD work, but it keeps going back to BOF, although, I checked, and I know the CURRENT rows is at 1001?! I still have not tried KeepITcool's method yet. I am off, but anxious to go back and try it. Even though, this is an ASP, I am using client-side VBScript to handle this, since OWC spreadsheet is staying in client side. "Jamie Collins" wrote: keepITcool wrote ... I realise OWC doesnt have the 65k row limit Does it required? The OP is clearing the 1000 rows each time, so they will never hit the max rows limit in Excel. 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 I'm not sure this is what the OP wanted. FWIW, for me the OP's code worked as expected i.e. for each page in the recordset, the sheet is cleared and the next 1000 rows appears in its place from the current bookmark rather than BOF. I was using a Jet ..mdb with the OLEDB provider for Jet 4.0 (i.e. fully supports bookmarks) on my local machine (i.e. no ASP). Jamie. -- |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
CopyFromRecordset with large amount of data
keepITcool wrote
Jamie? offtopic... the other day tou mentioned something re protected mdb's and xls insert.. is following what you need? Sub MakeFromLocked() Dim oCN As New ADODB.Connection If Dir("d:\fromlocked.xls") < "" Then Kill "d:\fromlocked.xls" oCN.Provider = "Microsoft.Jet.OLEDB.4.0" oCN.Properties("Extended Properties") = "Excel 8.0;Header=Yes;" oCN.Properties("Jet OLEDB:System database") = "D:\xlSupport.mdw" oCN.Properties("Data Source") = "d:\fromlocked.xls" oCN.Open UserID:="keepITcool", Password:="cia" oCN.Execute "Select * INTO Dump FROM [MS Access;Database=d: \locked.mdb;PWD=nsa].[Table1]" oCN.Close Set oCN = Nothing End Sub keepITcool, Thanks for this. What I actually meant, is it possible to put the path to the System database in the sql text? i.e. within the bracketed connection string: Select * INTO Dump FROM [<<connection string].Table1 I strongly suspect that it is not possible. You I think you can specify the UID and PWD but I assume it uses the 'default' workgroup file (whatever that means!) rather than a specific workgroup file. Or, looking at your example, perhaps it just ignores the UID and uses PWD for the database file's password. Whatever, I don't think I'd get it to work as I would've liked. Thanks again, Jamie. -- |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
CopyFromRecordset with large amount of data
keepITcool wrote ...
According to : http://msdn.microsoft.com/library/de...l=/library/en- us/odbc/htm/odbcjetsetting_options_programmatically_for_the_ac cess_drive r.asp the keyword is SYSTEMDB so this might work It's a good guess but not right :-( I think a completely different syntax is required e.g. the syntax in you link for the datasource is 'DBQ' but in the required syntax it is 'Database'. Cheers, Jamie. -- |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
CopyFromRecordset with large amount of data
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reformating large amount of data | Excel Discussion (Misc queries) | |||
Excel2007's performance over large amount of data( over 500,000 - 1M) | Excel Discussion (Misc queries) | |||
Changing large amount of Data | Excel Discussion (Misc queries) | |||
Large amount of data for plotting | Charts and Charting in Excel | |||
looping through and organizing a large amount of data | Excel Programming |