How to refresh all Pivot table

[Henrik Bergström]

Hi,

I have a ribbon and like a button to refresh all Pivottables.
Like Ctrl+Alt+F5

I tried.. 

xlApp.CalculateFullRebuild();

This force reload of all formulas but not PivotTables.

Br,

Henrik

[Govert van Drimmelen]

Hi Henrik,

 

Can you try xlApp.RefreshAll() ?

That should refresh the active workbook.

 

For old Excel you might need to get through the worksheets, and through the PivotTables on each, and call RefreshTable().

 

-Govert

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/ec474a45-2b98-4476-b676-f5a0d2e47450n%40googlegroups.com.

[Henrik Bergström]

Thanks it works,

Xl.Application xlApp = (Xl.Application)ExcelDnaUtil.Application;

var workbook = xlApp.ActiveWorkbook;

workbook.RefreshAll();

/Henrik

[David]

Hello,

I got a simple method which return current time this time do not update every secs ( didn't implement rtd ). 

By adding a button to the Ribbon I am trying trigger event that "RefreshAll" while clicking occured, but the formula doesn't reloading over again and the time remain as is. 

Any ideas ? 

 

ב-יום שני, 16 בנובמבר 2020 בשעה 12:39:57 UTC+2, hen...@borsdata.se כתב/ה:

[Govert van Drimmelen]

--------------------------------------------------
Excel-DNA is now registered on GitHub Sponsors.
When you sign up for a monthly contribution, it encourages ongoing support and development.
--------------------------------------------------

Hi David,

The question and answers here relate to PivotTables and are referring to the RefreshAll() method (Workbook.RefreshAll method (Excel) | Microsoft Learn)

If you look at the help, you'll see that it does the following: "Refreshes all external data ranges and PivotTable reports in the specified workbook."

That's not related to the sheet calculations or functions you call form formulas on the sheet..

To force a recalculation of cells for all the open workbooks, you can call the Application.CalculateFull method (Application.CalculateFull method (Excel) | Microsoft Learn):

The documentation says that it "Forces a full calculation of the data in all open workbooks."

    Xl.Application xlApp = (Xl.Application)ExcelDnaUtil.Application;

    xlApp.CalculateFull();

-Govert

[Kedar Kulkarni]

Just another answer to the same problem.. I usually use the .net equivalent of the following vba code. The above-mentioned methods work as well - so feel free to use any. , the following is just my preference. 

https://learn.microsoft.com/en-us/office/vba/api/excel.pivotcache

Public Sub RefreshPivotTables()
Dim pvtCache As PivotCache
For Each pvtCache In ActiveWorkbook.PivotCaches
    pvtCache.Refresh
Next
End Sub