Question

Here is the piece of code where i am trying to change the data source in existing pivot table,I have two pivot tables in both on i want to update the source data

 excel_app = win32com.client.dynamic.Dispatch("Excel.Application")
    excel_app.Interactive = False
    excel_app.Visible = False
    excel_app.DisplayAlerts = False
    xlBook = excel_app.Workbooks.Open(r C:Users70.PDeE_Python_ScriptSc_ext.xlsx )
    ws = xlBook.Worksheets[ Sheet2 ]
    ws.Unprotect()  # IF protected
    pivotCount = ws.PivotTables().Count
    print("pivot count is",pivotCount)

    for j in range(1, pivotCount + 1):
        ws.PivotTables(j).PivotCaches.Create(SourceType=xlDatabase, SourceData="C:Users70.PDesktopE_Python_Script[Sc_ext.xlsx]Sheet1!$A$1:$AC$4601", Version=xlPivotTableVersion14)

    # Put protection back on
    #ws.Protect(DrawingObjects=True, Contents=True, Scenarios=True, AllowUsingPivotTables=True)
    xlBook.Close(SaveChanges=1)

    del xlBook

    excel_app.Quit()
    del excel_app

Answer 1

Well, this is a really old question (I hope this help someone with the same problem)...

To change the source data of a pivot table with win32com.client:

Set the pivot table:

pivot_table = Sheet_name.PivotTables (“pivot table name”)

Set the options:

pivot_cache = WorkBook_name.PivotCaches().Create(SourceType = w32c.constants.xlDatabase, SourceData = data_Range, Version = The needed version)

WorkBook_name: The one with the pivot table.
data_Range:
- If the date is in the same sheet then at the end we add: .Address
- In case of an external reference then:
  .GetAddress(True, True, w32c.constants.xlA1, True)"

This line Refresh the pivot table with the new SourceData

pivot_table.ChangePivotCache(pivot_cache)

To change the source data of a pivot table with win32com.client:

Set the pivot table:

Set the options:

This line Refresh the pivot table with the new SourceData

友情链接