Pivot Table Looping

Posted by Cindy McCracken on December 03, 1999 1:47 PM

Hi everybody.

I am writing an Access function to refresh Excel pivot tables. My problem is that the function will not know how many pivot tables there are on a worksheet. So, I need it to somehow loop thru and refresh any it finds. Does anyone have a good way to do this? I tried using a pivot table collection, but the Refresh method won't work. I can use a table array but the function crashes after doing the last table, with message of Unable to get the Pivot Table property of the worksheet class.

Any ideas or suggestions would be welcomed.

Thanks,

Cindy

Posted by Ivan Moala on December 05, 1999 2:48 AM

Try using the PivotTables Collection.
Use the PivotTables method to return the PivotTables collection eg;

Worksheets("mysheet").PivotTables.Count
gives you the pivot table count for "myworksheet"
Use PivotTables(index),index = PivotTable index number to get a single PivotTable.

loop through each one or set as an object then loop
eg Set pvtTable = Worksheets("mysheet").PivotTables(index)
pvtTable.RefreshTable

Or something to that effect

Ivan

Posted by Cindy McCracken on December 06, 1999 5:36 AM

Thanks, Ivan.

I had considered this method, but I didn't know if
there was an easier way. Too bad you can't use
Collection.RefreshTables. That would be much faster to code.

Cindy

Posted by Richard on December 09, 1999 12:48 PM

Hi cindy:
You may want to just use this sample code in to refresh all pivot tables.

ActiveWorkbook.RefreshAll

Richard