Python Win32com Pivotcache.createpivotchart() Error: 'exception Occurred'

There is one other question from Sep 2017 that addresses this same problem but does not have an answer: create a pivotchart with python win32com I have tried several approaches to

Solution 1:

Thank you Boussif for finding the way on this.

Here is my working solution:

Generate a Pivot Chart from your Pivot Table

Using the Workbook object that we created earlier we can "insert" a Chart to our workbook and assign a name to our chart - name appears in the tab.

Because the Pivot Table is an object in the Pivot Cache - which is an object in the Workbook, the Chart object is added as a Pivot Chart and uses the Pivot Table as its the data.

Read the relevant documentation here:

https://docs.microsoft.com/en-us/office/vba/api/excel.workbook.charts

https://docs.microsoft.com/en-us/office/vba/api/excel.chart(object)

chrt_sheet = wb.Charts.Add()
chrt_sheet.Name = 'Pivot Chart'

Specify the Chart Type

The chart type corresponds to the same Chart Types that are available in the Excel program.

Choose from a list of enumerated types.

For example a 3D Column Chart has a value of xl3DColumn coded as follows:

win32c.xl3DColumn

Use the win32c object ew defined earlier to reference all constant values including enumerated lists.

Documented here:

https://docs.microsoft.com/en-us/office/vba/api/excel.chart.charttype

https://docs.microsoft.com/en-us/office/vba/api/excel.xlcharttype

chrt_sheet.ChartType =  win32c.xl3DColumn

Set the Chart Title

The sheet object will not have a ChartTitle property until to set HasTitle Property to True

chrt_sheet.HasTitle = Truechrt_sheet.ChartTitle.Text = "Chart Title"

Set the Color Scheme

ChartColor values 10 - 26 correspond to the Change Colors menu on the DESIGN tab of the CHART TOOLS ribbon.

chrt_sheet.ChartColor = 13

Specify a Chart Layout

Layout is optional

Layouts are the same as the list of Layouts to choose from in the Excel program.

In this case they are not provided as an enumerated list. So you have to provide a number for the layout. It will generally be from 1 to 10. Layouts are different depending on the respective Chart Type. To find out which layout to choose you will need to run the Excel program and try each one. If you hover your mouse over the Layout option it will display a "Tool Tip" with the name of the Layout. For example: 'Layout 7'. You would provide the number associated with your respective layout.

you use the ApplyLayout method to choose the Layout:

ApplyLayout(Layout, Chart Type)

Documented here:

https://docs.microsoft.com/en-us/office/vba/api/excel.chart.applylayout

chrt_sheet.ApplyLayout(7, win32c.xl3DColumn)

Specify a Chart Style

Style is optional

Documentation states that 1 to 48 are valid values. However the correct range depends on which Chart Type is chosen.

Depending on the Chart Type 201 to 352 is also valid.

To get the numbers numbers that match the style selections that are available for your Chart Type:

1. From the Developer Tab - Run a macro
2. From the Chart Design Tab - Select all the styles
3. From the Devloper Tab - Stop running the macro
4. From the Developer Tab - Edit the macro

This will reveal the correct values for your case

For the 3D Column Chart Type, the range is 286 to 297

Relevant documentation here:

https://docs.microsoft.com/en-us/office/vba/api/excel.chart.chartstyle

xlChartStyle = 294
chrt_sheet.ClearToMatchStyle
chrt_sheet.ChartStyle = xlChartStyle

Chart Style Experiment

If you are curious and want to see what each one looks like then run this code

hint: Remove the comments '#'

#import time#from time import sleep
​
#for xlChartStyle in range(286, 297):#    try:#        chrt_sheet.ClearToMatchStyle#        chrt_sheet.ChartStyle = xlChartStyle#        chrt_sheet.ChartTitle.Text = "Chart Style = "+str(xlChartStyle)# sleep(1)#    except pythoncom.com_error as error:# print("Chart Style = %s" % str(xlChartStyle))

​ Format Axis Labels

There are three Axis dimensions specified by the XlAxisType Enumeration

• X axis = xlCategory
• Y Axis = xlValue
• Z Axis = xlSeriesAxis (3D Charts only)

In this example we are also removing the Z Axis Tick Labels

chrt_sheet.Axes(win32c.xlCategory).AxisTitle.Text = "X Axis Title"
chrt_sheet.Axes(win32c.xlSeries).TickLabelPosition = win32c.xlNone
chrt_sheet.Axes(win32c.xlSeries).HasTitle = True
chrt_sheet.Axes(win32c.xlSeries).AxisTitle.Text = "Z Axis Title"
chrt_sheet.Axes(win32c.xlValue).AxisTitle.Text = "Y Axis Title"