Prev: Prevent deleting of cells
Next: Range updates
From: Jag on 10 Feb 2010 10:51
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 can't 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 aren't 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.
From: Dave Peterson on 10 Feb 2010 10:59
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 can't 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 aren't 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
 | 
Pages: 1
Prev: Prevent deleting of cells
Next: Range updates