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, May 01 '20 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, May 02 '20 at 06:43
Previously: https://stackoverflow.com/questions/44644002/pivot-cache-in-one-one-workbook-pivot-table-in-another-workbook — Tim Williams, May 02 '20 at 06:45

score 1 · Answer 1 · answered May 04 '20 at 11:55

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 04 '20 at 11:55

bradbase

409
6
9

1

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. – Waqas Muhammad May 05 '20 at 09:23
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. – bradbase May 05 '20 at 14:44
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 May 07 '20 at 09:16
1

`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 May 07 '20 at 09:34
1

`PCache = Wb.PivotCaches().Create(SourceType=win32c.xlDatabase, SourceData=PivotSourceRange,Version=win32c.xlPivotTableVersion14) PTable = PCache.CreatePivotTable(TableDestination=WsP.Range('B2'), TableName='RegisterPivot', DefaultVersion=win32c.xlPivotTableVersion14)` – Waqas Muhammad May 07 '20 at 09:35
I'm new to stackoverflow but is it possible to answer your question (eg; answer your own question) so I can upvote your code? – bradbase May 07 '20 at 12:54

score 0 · Answer 2 · answered Aug 18 '22 at 22:26

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.

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

2 Answers2