How to solve comerror: PivotFields method of PivotTable class failed?

Question

I'm trying to refresh a pivot table in excel and update existing filters using python's win32com. However, I'm hitting the error of:

com_error: (-214735267, 'Exception occured.', (0, 'Microsoft Excel', 'PivotFields method of PivotTable class failed', 'xlmain11.chm', 0, -2146827284), None)

Here's my code:

import win32com.client
filepath = r'test.xlsx'

office = win32com.client.Dispatch('Excel.Application')
office.DisplayAlerts = False
wb = office.Workbooks.Open(filepath, False, False, None)

ws = wb.Worksheets[0]

for i in range(1,3):
    ws.PivotTables(i).PivotCache().Refresh()

ws.PivotTables(1).PivotFields('Position_date').PivotFilters.Add2(34, None, '31/05/2021')
# 34 = xlAfterorEqualTo

wb.SaveAs(filepath, None, '', '')
office.Quit()

Appreciate any help I can get on this. Thanks.

I've had some weird quirks with win32com recently. eg. ws=wb.Worksheets[0] worked fine. Then I updated pywin32 to 301 (from 300), and replaced the application object creation line with office=win32com.client.gencache.EnsureDispatch('Excel.Application'). Once I did this, then Worksheets[0] failed: it now needed to be Worksheets[1] or Worksheets(1). Go figure. I switched back to win32com.client.Dispatch('Excel.Application') but still needed the 1-based worksheet index. One by-product was that I could now access win32com.client.constants.xlAfterOrEqualTo instead of using 34. — DS_London
– DS_London, Commented Sep 24, 2021 at 11:18
Also, double-check that Position_date is the right name. I get this error if I ask for a name that doesn't exist. The names aren't case-sensitive in my experience. Perhaps iterate through the PivotFields collection, and print out each Name, just to check. — DS_London
– DS_London, Commented Sep 24, 2021 at 11:26
Hi @DS_London, thanks for the tip to print out each Name, turns out I was referencing the wrong table. Have updated my code as below: ws.PivotTables(2).PivotFields('Position_date').PivotFilters.Add(34, None, '31/05/2021' However, I'm still getting the com_error. I'm using Add instead of Add2 as I'm using an old version which doesn't have Add2. Any idea on the com_error issue? — John peter
– John peter, Commented Sep 27, 2021 at 7:39
Can only suggest breaking it down. pvt = ws.PivotTables(2) then flds = pvt.PivotFields(), fld = flds('Position_date') etc rather than chain all the function calls together. Check each variable along the way (eg check flds.Count). — DS_London
– DS_London, Commented Sep 27, 2021 at 9:35

Gera Vassilenko · Accepted Answer · 2022-06-03 05:24:08Z

0

I had a problem with this. In my case I was getting the error because the pivot table field name did not match the source table field name.

answered Jun 3, 2022 at 5:24

Gera Vassilenko

1

Sign up to request clarification or add additional context in comments.

1 Comment

Community Over a year ago

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

How to solve comerror: PivotFields method of PivotTable class failed?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related