Updating Pivot Tables Dynamically
 

I'm currently looking to implement a solution to automate the process of
updating 5 pivot tables (one after another) on the same worksheet in an Excel
workbook.

Within the workbook there is a worksheet which is used as the data source
for the pivot tables.

If the number of items in the data source does not change the pivot table
updates without any problems. However if the number of items in the data
source increases the pivot tables start overlapping which causes errors. My
guess here is that you cant place pivot tables in a worksheet and have them
adjust their size dynamically.

One solution I've have tried to move the pivot table into separate work
sheets in the workbook. Then using C# and the Excel library, open the
workbook, update the pivot tables and then move them into a single page.
However this approach does not work because an error occurs when moving the
second pivot table and them overlapping. In addition using C# and the Excel
library is very error prone because the errors arent very descriptive and
Excel does not always close correctly.

Do you know of another way how we can resolve this issue? Ideally the Excel
workbook should not contain any macros, hence the reason why C# was used to
generate the spreadsheet.

Updating Pivot Tables Dynamically
 

I've always thought (with minor exceptions) that each pivottable deserves its
own worksheet. That way, the data can change drastically and I don't have to
worry about collisions with other stuff.

If I do have to put the results of specific views of multiple pivottables on a
single worksheet, I'll copy and paste|special values.

Those copies aren't pivottables and I still don't have to worry about
collisions.



Jag wrote:

I'm currently looking to implement a solution to automate the process of
updating 5 pivot tables (one after another) on the same worksheet in an Excel
workbook.

Within the workbook there is a worksheet which is used as the data source
for the pivot tables.

If the number of items in the data source does not change the pivot table
updates without any problems. However if the number of items in the data
source increases the pivot tables start overlapping which causes errors. My
guess here is that you cant place pivot tables in a worksheet and have them
adjust their size dynamically.

One solution I've have tried to move the pivot table into separate work
sheets in the workbook. Then using C# and the Excel library, open the
workbook, update the pivot tables and then move them into a single page.
However this approach does not work because an error occurs when moving the
second pivot table and them overlapping. In addition using C# and the Excel
library is very error prone because the errors arent very descriptive and
Excel does not always close correctly.

Do you know of another way how we can resolve this issue? Ideally the Excel
workbook should not contain any macros, hence the reason why C# was used to
generate the spreadsheet.

--

Dave Peterson