2

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.

John peter
  • 144
  • 1
  • 11
  • What version of pywin32 are you running? – DS_London Sep 24 '21 at 10:50
  • 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 Sep 24 '21 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 Sep 24 '21 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 Sep 27 '21 at 07: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 Sep 27 '21 at 09:35

1 Answers1

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.

Gera Vassilenko
  • 1
  • 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](/help/how-to-answer). – Community Jun 03 '22 at 07:23