VB & Pivot Table Refreshes

[Guest]

Hi All

(I'm using Excel2000 & VB)

I have a workbook of over one hundred sheets, each with at least one query or pivot table, but some have upwards of 20 pivot tables on a sheet. These have to be refreshed in a certain order (not in sheet order), and allowed to complete before going onto the next query or Pivot (no background refreshes), so a VB script seemed the best way to do the job

I have been able to generate the script that jumps through the sheets in the appropriate order, determines if a query table and/or pivot exists, and executes the appropriate refresh(es). This works fine

My problem? Too many refreshes! System bombs out with error from overloading the system with so many BACKGROUND (by default) refreshes

SO, I figured out how to correct the Query Table refresh by using the backgroundrefresh=false option, but cannot find an equivalent option for the Pivot Table refresh.

Here is the loop in question extracted from the VB script (an appropriate WITH statement been included but not shown for brevity)

If ptCount >= 1 The
For Each pt In .PivotTable
pt.PivotCache.BackgroundQuery = Fals
pt.RefreshTabl
Nex
End I

The "pt.refreshtable" works -- but I need some way to ensure it does not happen in the background. I found the method in the line above that one, but get an "application or object" error -- so I must not be using it correctly

I am new to VB, so appreciate any constructive pointers

Thanks
Janet Kaufman

 

[Debra Dalgleish]

You can set the BackgroundQuery property for pivot tables with an
external data source.

 

[Guest]

Thanks, Debra

most if not all of the Pivot Tables do not have external data sources (some do). They are based on a "raw data sheet" (i.e., an external query retrieves all the data onto one worksheet, then the Pivots are generated from that)
For these pivot tables, the Background Query property is not available

Any other suggestions?

Thanks! I've been struggling with this silly thing for several days