Refresh Excel Pivot Tables

0
votes

I have a workbook that has several tabs with pivot tables. I can put data on the tab that holds the data for each pivot. My problem is that I don't know how to refresh the pivot tables. I would assume that I would need to cycle through each sheet, check to see if there is a pivot table, and refresh it. I just can't find how to do that. All of the examples I find use win32 options, but I'm using a Mac and Linux.

I would like to achieve with openpyxl if possible.

3
pythonexcelpivot-tableopenpyxl
Possible duplicate of Using openpyxl to refresh pivot tables in Excle – Charlie Clark
I would prefer not to use a macro if possible. Also, there is a lot of data and my users won't want to wait for the macro to run and refresh the tables every time they open the workbook. Is there not an option for doing this in openpyxl? It looks like I can build and modify pivot tables but not refresh existing ones. – Eric Shreve

3 Answers

1
votes

Create A Macro from Developer -

Private Sub Worksheet_Change(ByVal Target As Range)

Worksheets("SheetName").PivotTables("PivotTableName").PivotCache.Refresh

End Sub

0
votes

Are you using "Format as a table" in your main data? When you do that you can append new information in the table and you don't have to change the new range in the pivot tables. Then you just have to select any cell in a pivot table and go to "Analyze" and select "Refresh All" it will refresh all in the workbook.

I made this Tutorial showing that. It is in Spanish but I am using the English version of Excel so it is easy to follow.

0
votes

Worksheets("SheetName").PivotTables("PivotTableName").PivotCache().Refresh()