CopyFromRecordset Question

[parityd]

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

 

[gocush]

Can you send us the applicable portion of your code: the part that copies to
multiple xl sheets ?

 

[parityd]

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

 

[parityd]

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