Pivot Table external XLS file source change and GETPIVOTDATA refresh
 

Hi,

I need to have a series of custom tables, specifically formatted in MS
Excel.

I decided I'll make them based on Pivot Table as a source data and
Excel links with GETPIVOTDATA do deliver the data to the proper MS
Excel format worksheet template. After filling with dynamic data from
pivots the template is saved as workbook needed.

Each table consist of around 100 of GETPIVOTDATA links. Each link has
it own pivot table in a 'working' worksheet. All the pivot tables are
based on the same external MS Excel file economizing on resources.
While making the pivot table I decided that the external data is
returned to Excel file.

I use VBA to manage thos Pivot tables. As a result the pages of the
pivot tables change delivering different results in Pivot table.

Unfortunately I have faced two difficult situations:

1) Data that I use seems to remember with the quite 'statically'. Yes,
its true that when I update the MS Excel source file and give refresh
on the pivot table, the pivot refreshes. But if I copy the folder both
with source and report files the new, copied report file will still
source from the original source file. To change it, today I have to
manually change the source for each pivot table that is quite
time-consuming. Unfortunately I do not know how to make those links
more dynamic (always for example take from source.xls in the same
folder) or update the source automatically with VBA.

I'd like to make it automatically as manual work always causes more
unexpected errors and its quite unefficient. Do you know how to solve
this?

2) When I change the pivot with VBA macro, the pivot itself changes,
but the links in custom formatted tables on other worksheet do not
'refresh'. I made a workaround by preparing special VBA procedure that
after VBA pivot table update, overwrites the links with identical
formulas in all cells. (Similar result to selecting the cell manually,
selecting the formula and pressing Enter). After that the links are
'refreshed' with the proper, chagned data.

This issue is done automatically, but maybe some of you know more
efficient / elegant solution?

My office is 2003 Professional, my system is WinXP.

Thanks,

Michal


--
mbobro
------------------------------------------------------------------------
mbobro's Profile: http://www.excelforum.com/member.php...fo&userid=1996
View this thread: http://www.excelforum.com/showthread...hreadid=559596