0

I can create an Excel 2010 pivot table using an input worksheet of greater than 65536 rows. When I try this in Python using win32com.client and 'Excel.Application' this fails with the message 

Exception: Type mismatch.

This was still a cryptic message but the fact that 65536 is the sweet spot suggests to me that the the code cannot handle Excel sheet ranges with more than this number of rows.

import win32api
import win32com.client
import pythoncom
Excel   = win32com.client.gencache.EnsureDispatch('Excel.Application')

wb = Excel.Workbooks.Open("output.xlsx")
sh_data = wb.Worksheets("DATA")
cl1 = sh_data.Cells(1,1)
cl2 = sh_data.Cells(65537,6)

PivotSourceRange = sh_data.Range(cl1,cl2)

sh_output = wb.Worksheets("OUTPUT")
outlc=sh_output.Cells(1,1)
PivotTargetRange=  sh_output.Range(outlc,outlc)
PivotTableName = 'ReportPivotTable'
try:
    PivotCache = wb.PivotCaches().Create(SourceType=win32c.xlDatabase, SourceData=PivotSourceRange, Version=win32c.xlPivotTableVersion14)
except pythoncom.com_error as error:
    print(error)
    print("Exception: " + win32api.FormatMessage(error.excepinfo[5]))

This is not a complete code snippet and the code fails at the PivotCache line with:

(-2147352567, 'Exception occurred.', (0, None, None, None, 0, -2147352571), 3)
Exception: Type mismatch.

Can I force it to treat the output.xlsx file as an Excel 2010 file to overcome this problem? Excel 2010 is the only version of Excel on my computer.

Thank you.

hubris
  • 1
  • 1
    Best to pass SourceData a String representing the address, not a range object. From the `PivotCaches.Create` VBA [docs](https://learn.microsoft.com/en-us/office/vba/api/excel.pivotcaches.create): "When passing a Range object, we recommend that you either use a string to specify the workbook, worksheet, and cell range, or set up a named range and pass the name as a string. Passing a Range object may cause "type mismatch" errors unexpectedly." – BigBen Nov 07 '19 at 15:35
  • Also relevant: https://stackoverflow.com/questions/25606962/pivotcache-create-unable-to-handle-large-range – BigBen Nov 07 '19 at 15:37

0 Answers0