Hi,

I have a report scheduled to run every week by JP1. The excel will login
to Oracle database using OO4O, output the data into a worksheet. From there,
the macro will instruct the pre-build pivot table to do a refresh, and then
copy the result into another Excel file. My worry is that if the data
extracted is more than 65K rows, I will encounter problem, as that is Excel
limit for Excel XP/2002.

Is there any workaround for this? Please don't ask me to upgrade to
2007, as it will take quite some time for us to upgrade.
1) Can we spread the data over several worksheets, and build a pivot
table from there, and not using consolidated data? If yes, please explain to
me how to achieve it.

2) If (1) is not achieveable, can I write the Dynaset directly to Pivot
table cache? How shall I write the Dynaset to the pivot table cache?

3) If (2) is not achieveable, how can I convert the Dynaset to Array
recognised by the Macro, so that the pivot table can be refreshed from the
Array?

I have tried to set the pivot table to obtain the data directly from
the database via ODBC, and will do a refresh everytime it is open. But the
Excel will prompt me whether to open refresh the data everytime I open the
Excel. Is there a way to overcome it? I am using JP1 to trigger off the
report. How can I refresh the pivot table data using Macro?

Please help. Thanks.

Regards.