Using win32com.client library, unable to create PivotTable from PivotCache created using range in some other workbook

Question

I am trying to create a Pivot Table in workbook named "Model.xlsx" using PivotCache created from dataRange in other workbook named "Reports.xlsx"

When I create the Pivot Table in the same workbook (Reports.xlsx) from which dataRange is created then Pivot Table is created successfully but when i try to create Pivot Table in other workbook it gives error "exception occured"

My Code is too lengthy that is why i am not sharing it coz it might confuse the reader. I hope that i have sufficiently elaborated the question.

Without code how can we know what the problem is? We don't need the whole thing, just the relevant parts. — Tim Williams
– Tim Williams, Commented May 1, 2020 at 20:59
Hey Tim, is it possible to create a pivot cache in a workbook and then use that pivot cache to create a pivot table in another workbook? have u ever experienced that? — Waqas Muhammad
– Waqas Muhammad, Commented May 2, 2020 at 6:43

bradbase · Accepted Answer · 2020-05-04 11:55:19Z

1

While using COM, pivot tables need to be created "in Excel, by Excel". I have used xlwings (which, at its core, is a COM wrapper). The idea is to create a pivot cache and use the pivot cache it to generate the pivot table.

import xlwings as xw
from xlwings import constants

wb = xw.Book.caller()

pivot_table = xw.sheets.add(name='Pivot Table', after='Sheet1')

PivotTableName = 'ReportPivotTable'

PivotCache = wb.api.PivotCaches().Create(SourceType=constants.PivotTableSourceType.xlDatabase, SourceData=PivotSourceRange.api, Version=constants.PivotTableVersionList.xlPivotTableVersion14)

PivotTable = PivotCache.CreatePivotTable(TableDestination="'Pivot Table'!R1C1", TableName=PivotTableName, DefaultVersion=constants.PivotTableVersionList.xlPivotTableVersion14)
PivotTable.PivotFields('Last Name').Orientation = constants.PivotFieldOrientation.xlRowField
PivotTable.PivotFields('Last Name').Position = 1
PivotTable.PivotFields('Project Code').Orientation = constants.PivotFieldOrientation.xlRowField
PivotTable.PivotFields('Project Code').Position = 2
PivotTable.PivotFields("total").Orientation = constants.PivotFieldOrientation.xlDataField

When I was doing this I found the Microsoft help for pivot tables invaluable.

answered May 4, 2020 at 11:55

bradbase

5196 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Waqas Muhammad Over a year ago

Hey BradBase, i suggest you to use win32com library for excel automation in Python. i have been using it and its awesome. You can do anything with win32com that could possibly be done by VBA. Furthermore Microsoft's official documentation for Excel VBA could also be used without any issue. However there is no much documentation available on the internet for this.

bradbase Over a year ago

Thanks Waqas. Are you able to post a small example solution with win32com so we can see how to do it in both worlds? xlwings gives me some other services that I'm usually interested in, but knowing the win32com methods would be great too.

Waqas Muhammad Over a year ago

Off course BradBase. Below are some snippets. In my opinion win32com is much simpler and awesome thing about it is that we actually access VBA methods and functions.

Waqas Muhammad Over a year ago

ExcelApp = win32com.client.gencache.EnsureDispatch('Excel.Application') win32c = win32com.client.constants Wb = Excel.Workbooks.Open('C:\\users\\wyousuf\\desktop\\purchaseRegisterJan20.xlsx') Excel.Visible = True Ws = Wb.Sheets('PR Jan20')

Waqas Muhammad Over a year ago

PCache = Wb.PivotCaches().Create(SourceType=win32c.xlDatabase, SourceData=PivotSourceRange,Version=win32c.xlPivotTableVersion14) PTable = PCache.CreatePivotTable(TableDestination=WsP.Range('B2'), TableName='RegisterPivot', DefaultVersion=win32c.xlPivotTableVersion14)

|

abpc1 · Accepted Answer · 2022-08-18 22:26:14Z

Adding to bradbase's answer:

One thing I found that is helpful is to record a macro in Excel to see all the API items that are used in generating the pivot table.

For example, to add a filter to a pivot table, you need to enter the following (note that in my case I have a column header "Type" that I want to use a a Pivot Table Filter:

PivotTable.PivotFields("Type").Orientation = constants.PivotFieldOrientation.xlPageField
PivotTable.PivotFields("Type").EnableMultiplePageItems = True
PivotTable.PivotFields("Type").PivotItems("Filter1").Visible = False
PivotTable.PivotFields("Type").PivotItems("Filter2").Visible = False
PivotTable.PivotFields("Type").PivotItems("Filter3").Visible = False
PivotTable.PivotFields("Type").PivotItems("Filter4").Visible = True

Here only "Filter4" will be visible while all other filters are unselected and hence not visible.

Collectives™ on Stack Overflow

Using win32com.client library, unable to create PivotTable from PivotCache created using range in some other workbook

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related