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.

Please help. Thanks.

"tts" wrote:

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.

Do you really need all of the source data, or could you adjust your query to
report a more summarized version instead (which might all fit on one sheet)?
Oracle has a number of summary/rollup functions which might be useful here.

And what's JP1?

Tim




"tts" wrote in message
...
Please help. Thanks.

"tts" wrote:

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.

Hi,

Thanks for the response. Yes, I would need to extract all the source
data, as I want the user to do their own grouping, crunching of data, instead
of we have to include what is required for the user.

Anyway, I have find out a workaround solution. The data will first be
spool into a CSV file. In the Excel macro, I will build a pivot table based
on the CSV file, instead of getting directly from the database, and the pivot
table will be generated in a new Excel workbook. Hopefully, I can overcome
the 65K barrier.

"Tim Williams" wrote:


Do you really need all of the source data, or could you adjust your query to
report a more summarized version instead (which might all fit on one sheet)?
Oracle has a number of summary/rollup functions which might be useful here.

And what's JP1?

Tim




"tts" wrote in message
...
Please help. Thanks.

"tts" wrote:

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.