Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
CopyFromRecordset Question
HI,
I have a VB6 application that queries an Oracle Table and export the data to an Excel 2003 workbook using the CopyFromRecordset method. I have a procedure that will export the data into multiple worksheets if there are more than 65536 rows. This works fine if the recordset has not more than 65536 rows. When the recordset has more than 65536 rows, the CopyFromRecordset method still only copies the first 65536 rows onto the next worksheet. I am using ADO 2.7 and Oracle version is 8i. However when the above application queries an Access database that returns more than 65536 rows (eg. 150,000 records), the CopyFromRecordset method works fine copying are the 150,000 records onto 3 worksheets. Anyone who has run into this problem and may have a solution please advise. Thank you parityd |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
CopyFromRecordset Question
Can you send us the applicable portion of your code: the part that copies to
multiple xl sheets ? "parityd" wrote: HI, I have a VB6 application that queries an Oracle Table and export the data to an Excel 2003 workbook using the CopyFromRecordset method. I have a procedure that will export the data into multiple worksheets if there are more than 65536 rows. This works fine if the recordset has not more than 65536 rows. When the recordset has more than 65536 rows, the CopyFromRecordset method still only copies the first 65536 rows onto the next worksheet. I am using ADO 2.7 and Oracle version is 8i. However when the above application queries an Access database that returns more than 65536 rows (eg. 150,000 records), the CopyFromRecordset method works fine copying are the 150,000 records onto 3 worksheets. Anyone who has run into this problem and may have a solution please advise. Thank you parityd |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
CopyFromRecordset Question
Below is the code segment that makes multiple xl sheets.
Do While Not padoRS.EOF = True 'Copy a maximum of 65530 records to the Excel worksheet starting on row 2 If plngRecCount 65530 Then pobjWKBook.Sheets.Add after:=pobjWKBook.ActiveSheet pobjWKBook.ActiveSheet.Name = "Page " & pintPageNum pintPageNum = pintPageNum + 1 Set pobjWS = pobjWKBook.ActiveSheet For pintColCount = 1 To pintFldCount pobjWS.Cells(1, pintColCount).Value = padoRS.Fields(pintColCount - 1).Name Next End If pobjWS.Range(pobjWS.Cells(2, 1), _ pobjWS.Cells(65530, pintFldCount)).CopyFromRecordset padoRS, 65530, pintFldCount plngRecCount = plngRecCount + 65530 Loop |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
CopyFromRecordset Question
parityd wrote: Below is the code segment that makes multiple xl sheets. 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 more_than_65536() 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. -- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
CopyFromRecordset Question
Using CopyFromRecordset on a recordset retrieved from a Microsoft
Access table with more than 65536 records will work, it does moved the cursor to the 65537th record and the subsequent worksheet will be populated beginning with the 65537th record. However, when using CopyFromRecordset on a recordset retrieved from an Oracle table with more than 65536 records, the subsequent worksheet will be populated beginning with the first record again. I was hoping to use CopyFromRecordset (due to performance) instead of writing row by row. Thanks parityd |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CopyFromRecordset Problem | Excel Discussion (Misc queries) | |||
CopyFromRecordset method | Excel Programming | |||
CopyFromRecordset does nothing | Excel Programming | |||
CopyFromRecordset does nothing | Excel Programming | |||
Copyfromrecordset Bug ? | Excel Programming |