Creating pivot table in Excel using python causes pywintypes.com_error

As this is the one of the first Google hits when searching for Excel pivot tables from Python, I post my example code. This code generates a simple pivot table in Excel through a COM server, with some basic filters, columns, rows, and some number formatting applied.I hope this helps someone not to waste half a day on it (like I did...)

import win32com.clientExcel   = win32com.client.gencache.EnsureDispatch('Excel.Application') # Excel = win32com.client.Dispatch('Excel.Application')win32c = win32com.client.constantswb = Excel.Workbooks.Add()Sheet1 = wb.Worksheets("Sheet1")TestData = [['Country','Name','Gender','Sign','Amount'],             ['CH','Max' ,'M','Plus',123.4567],             ['CH','Max' ,'M','Minus',-23.4567],             ['CH','Max' ,'M','Plus',12.2314],             ['CH','Max' ,'M','Minus',-2.2314],             ['CH','Sam' ,'M','Plus',453.7685],             ['CH','Sam' ,'M','Minus',-53.7685],             ['CH','Sara','F','Plus',777.666],             ['CH','Sara','F','Minus',-77.666],             ['DE','Hans','M','Plus',345.088],             ['DE','Hans','M','Minus',-45.088],             ['DE','Paul','M','Plus',222.455],             ['DE','Paul','M','Minus',-22.455]]for i, TestDataRow in enumerate(TestData):    for j, TestDataItem in enumerate(TestDataRow):        Sheet1.Cells(i+2,j+4).Value = TestDataItemcl1 = Sheet1.Cells(2,4)cl2 = Sheet1.Cells(2+len(TestData)-1,4+len(TestData[0])-1)PivotSourceRange = Sheet1.Range(cl1,cl2)PivotSourceRange.Select()Sheet2 = wb.Worksheets(2)cl3=Sheet2.Cells(4,1)PivotTargetRange=  Sheet2.Range(cl3,cl3)PivotTableName = 'ReportPivotTable'PivotCache = wb.PivotCaches().Create(SourceType=win32c.xlDatabase, SourceData=PivotSourceRange, Version=win32c.xlPivotTableVersion14)PivotTable = PivotCache.CreatePivotTable(TableDestination=PivotTargetRange, TableName=PivotTableName, DefaultVersion=win32c.xlPivotTableVersion14)PivotTable.PivotFields('Name').Orientation = win32c.xlRowFieldPivotTable.PivotFields('Name').Position = 1PivotTable.PivotFields('Gender').Orientation = win32c.xlPageFieldPivotTable.PivotFields('Gender').Position = 1PivotTable.PivotFields('Gender').CurrentPage = 'M'PivotTable.PivotFields('Country').Orientation = win32c.xlColumnFieldPivotTable.PivotFields('Country').Position = 1PivotTable.PivotFields('Country').Subtotals = [False, False, False, False, False, False, False, False, False, False, False, False]PivotTable.PivotFields('Sign').Orientation = win32c.xlColumnFieldPivotTable.PivotFields('Sign').Position = 2DataField = PivotTable.AddDataField(PivotTable.PivotFields('Amount'))DataField.NumberFormat = '#\'##0.00'Excel.Visible = 1wb.SaveAs('ranges_and_offsets.xlsx')Excel.Application.Quit()

Found from PivotTable.AddDataField method (Excel) that in expression .AddDataField(Field, Caption, Function) only Field is required and the other two parameters are optional. I removed them and the code works fine!