I have a report template that uses getpivotdata from pivot tables hidden in
the background. There are many templates in use and refreshing each one was
becoming very time consuming. I hoped to use one central set of pivot tables
and point all of the indivual reports to this workbook using getpivotdata. I
discovered that the data vanishes unless the central "pivot sheet" is open.
Is there any easy way around this? Many thanks in advance for your
willingness to share your acumen.

Nope the pivot has to be open in order to read the data out of it with
GetPivotData. That being said any number you can generate with a pivot table
can also be generated with a SumProduct Formula wich does not require the
open workbook... Check out this link...

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
--
HTH...

Jim Thomlinson


"scharf" wrote:

I have a report template that uses getpivotdata from pivot tables hidden in
the background. There are many templates in use and refreshing each one was
becoming very time consuming. I hoped to use one central set of pivot tables
and point all of the indivual reports to this workbook using getpivotdata. I
discovered that the data vanishes unless the central "pivot sheet" is open.
Is there any easy way around this? Many thanks in advance for your
willingness to share your acumen.

Thanks Jim. The pivot tables that I use are pulling data from Access
queries. The queries pull from linked tables (ODBC to SQL). I am unable to
guarantee that the field in "column C" will remain there (e.g. the data is
very dynamic). Will this approach fit into that model? I also need to pull
back the total number of records that satisfy 3+ criteria... I am not very
familiar with sumproduct, so I want to be sure that this method will ring the
bell. Thanks very much for your guidance.


"Jim Thomlinson" wrote:

Nope the pivot has to be open in order to read the data out of it with
GetPivotData. That being said any number you can generate with a pivot table
can also be generated with a SumProduct Formula wich does not require the
open workbook... Check out this link...

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
--
HTH...

Jim Thomlinson


"scharf" wrote:

I have a report template that uses getpivotdata from pivot tables hidden in
the background. There are many templates in use and refreshing each one was
becoming very time consuming. I hoped to use one central set of pivot tables
and point all of the indivual reports to this workbook using getpivotdata. I
discovered that the data vanishes unless the central "pivot sheet" is open.
Is there any easy way around this? Many thanks in advance for your
willingness to share your acumen.