How to create an Excel pivot table from a table (ListObject object) via PyWin32?

Question

I'm trying to automate Excel report generation in Python and I would really like to create a pivot table from a proper table, which equates to the ListObject object in COM. I used several code chunks I found in the web, and it goes like this:

excel = win32.gencache.EnsureDispatch('Excel.Application')
 
excel.Visible = True 
 
try:
    wb = excel.Workbooks.Open("\\".join((os.getcwd(), file)))
except com_error as e:
    if e.excepinfo[5] == -2146827284:
        print(("Filepath invalid: {filename}"))
    else:
        raise e
    sys.exit(1)

Now I need to create a proper table (ListObject), not merely a range, to ease future manual updates of the file:

table_db_property = wb.ActiveSheet.ListObjects.Add()
table_db_property.Name = "SummaryTable"
table_db_property.TableStyle = "TableStyleMedium2"


ws = wb.Worksheets.Add()
ws.Name = "People Summary"

With that out of the way, now I would like to create the pivot table using this table as the source. But this is when the code crashes:

PivotCache = wb.PivotCaches().Create(SourceType=win32c.xlDatabase, SourceData=table_db_property.Range, Version=win32c.xlPivotTableVersion14)

PivotTargetRange= wb.Worksheets("People Summary").Range("C5")
 
PivotTable = PivotCache.CreatePivotTable(TableDestination=PivotTargetRange, TableName="Test", DefaultVersion=win32c.xlPivotTableVersion14)

The displayed error is the following (or a close variation of it):

---------------------------------------------------------------------------
com_error                                 Traceback (most recent call last)
<ipython-input-175-4216b80db72c> in <module>
----> 1 PivotTable = PivotCache.CreatePivotTable(TableDestination=PivotTargetRange, TableName="Test", DefaultVersion=win32c.xlPivotTableVersion14)
 
~\AppData\Local\Temp\42\gen_py\3.8\00020813-0000-0000-C000-000000000046x0x1x9\PivotCache.py in CreatePivotTable(self, TableDestination, TableName, ReadData, DefaultVersion)
     42         # Result is of type PivotTable
     43         def CreatePivotTable(self, TableDestination=defaultNamedNotOptArg, TableName=defaultNamedOptArg, ReadData=defaultNamedOptArg, DefaultVersion=defaultNamedOptArg):
---> 44         ret = self._oleobj_.InvokeTypes(1836, LCID, 1, (9, 0), ((12, 1), (12, 17), (12, 17), (12, 17)),TableDestination
     45             , TableName, ReadData, DefaultVersion)
     46                 if ret is not None:
 
com_error: (-2147352567, 'Exception occurred.', (0, None, None, None, 0, -2146827284), None)

I don't know how to debug this. I've tried switching the SourceData argument from the PivotCache function to table_db_property.Range() and tabla_db_property.DataBodyRange with no success.

Any hint to solve this problem, please?

Answer 1

These are guesses, but try using the table name for SourceData:

SourceData='SummaryTable'

and an R1C1 reference for the TableDestination, including the sheet name:

TableDestination="'People Summary'!R5C3"