Making a Pivot Table with pywin32, seeing errors with win32com.client.constants

Question

I do a lot of excel work and am looking to migrate from VBA to python. I'm trying to create a pivot table and add the necessary pivot fields. I followed example code from this site but am stuck trying to get the win32com.client.constants to work with xlPageField.

import win32com.client as win32
import sys
import logging

win32c = win32.constants

logging.basicConfig(level=logging.INFO)

loc = "C:/Users/Me/Documents/Test.xlsx"
xl = win32.Dispatch('Excel.Application')
#xl.Visible = True
wb = xl.Workbooks.Open (loc)
rd = wb.Sheets("Raw Data")



def pivot_table(wb: object, ws1: object, pt_ws: object, ws_name: str, pt_name: str, pt_rows: list, pt_cols: list, pt_filters: list, pt_fields: list):

    #clear previous pivot tables
    try:
        pt_ws.PivotTables(1)
    except:
        pass
    else:
        pt_ws.PivotTables(1).TableRange2.Clear()

    # pivot table location
    pt_loc = len(pt_filters) + 2
    
    # grab the pivot table source data
    #pc = wb.PivotCaches().Create(SourceType=win32.xlDatabase, SourceData=ws1.UsedRange)
    pc = wb.PivotCaches().Create(1, SourceData=ws1.UsedRange)

    # create the pivot table object
    pc.CreatePivotTable(TableDestination=f'{ws_name}!R{pt_loc}C1', TableName=pt_name)

    # selecte the pivot table work sheet and location to create the pivot table
    pt_ws.Select()
    pt_ws.Cells(pt_loc, 1).Select()

    # Sets the rows, columns and filters of the pivot table

    for field_list, field_r in ((pt_filters, win32c.xlPageField), (pt_rows, win32c.xlRowField), (pt_cols, win32c.xlColumnField)):
        for i, value in enumerate(field_list):
            pt_ws.PivotTables(pt_name).PivotFields(value).Orientation = field_r
            pt_ws.PivotTables(pt_name).PivotFields(value).Position = i + 1
            
    # Sets the Values of the pivot table
    for field in pt_fields:
        pt_ws.PivotTables(pt_name).AddDataField(pt_ws.PivotTables(pt_name).PivotFields(field[0]), field[1], field[2]).NumberFormat = field[3]

        
    # Visiblity True or Valse
    pt_ws.PivotTables(pt_name).ShowValuesRow = True
    pt_ws.PivotTables(pt_name).ColumnGrand = True

ws1 = rd
ws2_name = 'Piv'
ws2 = wb.Sheets('Piv')
pt_name = 'example'
pt_rows = ['End Customer']
pt_cols = ['Stage']
pt_filters = ['Project Status']
pt_fields = ['Schedule Quantity']

pivot_table(wb, ws1, ws2, ws2_name, pt_name, pt_rows, pt_cols, pt_filters, pt_fields)

The error that gets returned is:

 for field_list, field_r in ((pt_filters, win32c.xlPageField), (pt_rows, win32c.xlRowField), (pt_cols, win32c.xlColumnField)):

File "C:\Users\Me\AppData\Local\Programs\Python\Python311\Lib\site-packages\win32com\client\__init__.py", line 232, in __getattr__
    raise AttributeError(a)
AttributeError: xlPageField

Making a Pivot Table with pywin32, seeing errors with win32com.client.constants

0 Answers0