Need your help to change the filter for a pivot table in Excel using Python. Refreshing Pivot Tables are working I can clear filters but When I try to change the filter, the Python program fails. I have tried it with both libraries like win32com and xlwings but the change filter process does not work.
OS Windows
Versions of xlwings - 0.32.0 or win32com, Excel - Version 2405 Office 365 and Python - 3.9.6
Issue Description
I am working on a POC to refresh and make filter changes Excel workbook connected to Analysis Services i.e. Atscale data cube. The part to refresh the pivot table is working as expected but when I try to update the filter the task fails abruptly.
Scenario for a filter change, the pivot table has a filter Time that is set to the prior date which we want to change to the prior week.
Links: Connect to Atscale
Exception: (0, 'Microsoft Excel', 'Unable to set the CurrentPage property of the PivotField class', 'xlmain11.chm', 0, -2146827284)
Minimal code sample to reproduce the issue
Your code here
import xlwings as xw try: # Open the Excel
workbook wb = xw.Book(r'pivot_workbook.xlsx') # Access the specific worksheet containing the pivot table
sheet = wb.sheets['Sheet1'] # Replace 'Sheet1' with your sheet name # Access the pivot table
pivot_table = sheet.api.PivotTables('PivotTable1') # Replace 'PivotTable1' with your pivot table name
# Change the filter value (for example, filtering on "Time") filter_field = pivot_table.PivotFields('Time')
filter_field.CurrentPage = 'Prior Date' # Refresh the pivot table after changing the filter pivot_table.RefreshTable() # Save and close the workbook wb.save() wb.close() except Exception as e:
print("Exception", e)