Import Oracle Dbase over 65k rows
 

Hi,

Everyday I used to import the oracle database (Sales Data - Invoice
Numberwise) in Excel.
Now the data is almost about 1,50,000 rows and excel have a limitations of
65000 rows.
Is there any solution, say if the recordset data is above 65000 rows then
excel should
add another sheet & copy the data upto 65000 rows in one sheet & above 65000
is in
another sheet.

Help in this regard will be hieghly appreciated.



--
Thanks,
Vikram P. Dhemare

Import Oracle Dbase over 65k rows
 

You could adapt the code for importing files with more than 64K lines
described on and dowloadable from
http://www.cpearson.com/excel/ImportBigFiles.htm . The code is written for
text and CSV files, but should easily adaptable to any type of data source.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

"Vikram Dhemare" wrote in message
...
Hi,

Everyday I used to import the oracle database (Sales Data - Invoice
Numberwise) in Excel.
Now the data is almost about 1,50,000 rows and excel have a limitations of
65000 rows.
Is there any solution, say if the recordset data is above 65000 rows then
excel should
add another sheet & copy the data upto 65000 rows in one sheet & above
65000
is in
another sheet.

Help in this regard will be hieghly appreciated.



--
Thanks,
Vikram P. Dhemare

Import Oracle Dbase over 65k rows
 

Mr. Chip Pearson,

Thanks for taking the time to post. Your knowledge is helpful in so many
ways. Procedure, Function, Implementation code, All with helpful and great
comments.

Thanks,

Christmas May

"Chip Pearson" wrote:

You could adapt the code for importing files with more than 64K lines
described on and dowloadable from
http://www.cpearson.com/excel/ImportBigFiles.htm . The code is written for
text and CSV files, but should easily adaptable to any type of data source.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

"Vikram Dhemare" wrote in message
...
Hi,

Everyday I used to import the oracle database (Sales Data - Invoice
Numberwise) in Excel.
Now the data is almost about 1,50,000 rows and excel have a limitations of
65000 rows.
Is there any solution, say if the recordset data is above 65000 rows then
excel should
add another sheet & copy the data upto 65000 rows in one sheet & above
65000
is in
another sheet.

Help in this regard will be hieghly appreciated.



--
Thanks,
Vikram P. Dhemare

Import Oracle Dbase over 65k rows
 

If you use ADO to retrieve your data,
1. Create the Recordset Object
2. Set the PageSize property to 65, 535 (first row is for column names)
3. Open your recordset
4. Loop though the Recordset by Page, adding a new worksheet for every page
a. write the column names to row 2
b. write all the data for the page at A2 using the CopyFromRecordset object

Import Oracle Dbase over 65k rows
 

Thanks a lot. It is really very much helpful to me.
--
Thanks,
Vikram P. Dhemare


"AA2e72E" wrote:

If you use ADO to retrieve your data,
1. Create the Recordset Object
2. Set the PageSize property to 65, 535 (first row is for column names)
3. Open your recordset
4. Loop though the Recordset by Page, adding a new worksheet for every page
a. write the column names to row 2
b. write all the data for the page at A2 using the CopyFromRecordset object