Changing Filter for Pivot Table in Excel Workbook connected to Data Cubes

[Jamesvasanth21]

OS Windows

Versions of xlwings - 0.32.0 and pywin32(fails with both pywin32 and xlwings) , Excel - Version 2405 Office 365 and Python - 3.96

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)

Include a minimal code sample to reproduce the issue (and attach a sample workbook if required!)

# 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)

[Avasam]

Where is pywin32 used in your minimal repro? And which version? Did you copy xlwings/xlwings#2504 and forget to change the code?

[Jamesvasanth21]

I’ve already pointed out that the issue occurs with both Xlwings and Pywin32. Since Xlwings uses Pywin32 to interact with Excel Pivot Tables, the problem is clearly with Pywin32, not Xlwings. Given the top-tier quality of the Pywin32 documentation, it's no surprise that this issue has arisen. I've taken the time to create an issue for this. The least you could do is thoroughly review it before dismissing it as invalid

Version: pywin32=306
Code Snippet:

import win32com.client

# Open Excel application
excel = win32com.client.DispatchEx("Excel.Application")

# Set to False to prevent Excel from becoming visible on screen
excel.Visible = False

# Open the workbook
workbook = excel.Workbooks.Open(r"pivot_workbook.xlsx")

# Access the specific worksheet
worksheet = workbook.Sheets("Sheet1")  # Replace "Sheet1" with your sheet name

# Access the pivot table
pivot_table = worksheet.PivotTables("PivotTable1")  # Replace "PivotTable1" with your pivot table name

# Change the filter on a specific pivot field (e.g., filter 'FieldName' to show only 'FilterValue')
field_name = "Time"  # Replace with the actual field name
filter_value = "Prior Date"  # Replace with the desired filter value

# Apply the filter
pivot_table.PivotFields(field_name).CurrentPage = filter_value

# Save and close the workbook
workbook.Save()
workbook.Close()
excel.Quit()

Error:
'Microsoft Excel', 'Unable to set the CurrentPage property of the PivotField class'