Excel Pivot Tables UDF

[sudeepjd]

I was looking for a UDF using which I could Add and Update Pivot tables and Pivot Charts in Excel easily and could not find one that I could use. So I build this UDF. 

It has the following functions :

_ExcelPivot_CreateCache  ; Easily Create a pivot table data cache from a Sheet
_ExcelPivot_CreateTable  ; Create a table from a cache at a specified location on the sheet
_ExcelPivot_RefreshTable ; Refresh the datatable data with a new cache
_ExcelPivot_AddField     ; Add a Field and Aggregate function to the Datatable
_ExcelPivot_AddFilter    ; Adds in the Filter to a specific field
_ExcelPivot_ClearFilter  ; Removes the filter for a field or all the filters in the table
_ExcelPivot_GetRange     ; Get specific areas of a Pivot as a Range Object
_ExcelPivot_AddChart     ; Add a Pivot Chart linked to a specific Pivot table

Attached the UDF to this post.

Please do let me know if I can improve or add additional functions to it.

A detailed example on the usage is below. The excel file and the example can be downloaded from the Example.zip file attached.

#include "ExcelPivot.au3"

$oExcel = _Excel_Open()
$oBook = _Excel_BookOpen($oExcel, @ScriptDir & "\TestPivot.xlsx")

;Create a Sheet to put the pivot into
$pSheet = _Excel_SheetAdd($oBook, -1, False, 1)
$pSheet.Name = "Pivot"

;Get the cache for the pivot table
$pCache = _ExcelPivot_CreateCache($oBook, "Data")

;Add in the Pivot Table from the Cache
_ExcelPivot_CreateTable($pCache, $pSheet, "A1", "FruitsPivot")

;Add in the Fields into the Pivot
_ExcelPivot_AddField($pSheet, "FruitsPivot", "Category", "Filter")
_ExcelPivot_AddField($pSheet, "FruitsPivot", "Product", "Row")
_ExcelPivot_AddField($pSheet, "FruitsPivot", "Amount", "Value", "Sum", 1)

;Add in a Running total to the Pivot
_ExcelPivot_AddField($pSheet, "FruitsPivot", "Amount", "Value", "Sum", 2, "PercentageRunningTotal", "Product")

;Filter only the fruits
_ExcelPivot_Filter($pSheet, "FruitsPivot", "Category", "Fruit")

;Draw a Paretto Chart
$chart = _ExcelPivot_AddChart($oBook, $pSheet, "FruitsPivot", "ColumnClustered", "Paretto", "E2", 570)
$chart.Chart.FullSeriesCollection(1).ApplyDataLabels
$chart.Chart.FullSeriesCollection(2).ChartType = 4 ;Change the percentage to a line graph
$chart.Chart.FullSeriesCollection(2).AxisGroup = 2 ;Move it to secondary axis
$chart.Chart.Axes(2, 2).MaximumScale = 1 ;Adjust to scale to 100% max

 

 

ExcelPivot.au3 Example.zip

Edited November 19, 2020 by sudeepjd
Added Further error checking and added an Example

[jimmy123j]

Thanks for UDF for pivot, it'll be useful to me

Could you share some example ?

[sudeepjd]

On 11/16/2020 at 9:50 PM, jimmy123j said:

Could you share some example ?

I have added the example into the first post.

[antmar904]

Hello and thank you for this.

I receive a csv report on a monthly basis in which I then need to create a pivot table.  Would this UDF be able to help me automate that?  It's basically a line chart that I need to create from the csv report.

[antmar904]

Hi.

I just remembered this UDF here: ExcelChart UDF - AutoIt Example Scripts - AutoIt Forums (autoitscript.com)

@water Would your UDF be able to help me create a pivot line chart from a csv report that I run monthly?  I also have a pivot chart template (.crtx) which I would like to use.

[water]

I have never tried to combine a Pivot table with my ExcelChart UDF.
So just give it a try. If you run into problems I will do my very best to assist.

[SkysLastChance]

Is it possible to do Calculated Fields using this UDF?

 

 

[MrTWist]

Issues I have found:

1. The function headers and the function signatures do not match. Example: _ExcelPivot_CreateTable indicates "$sSheetName", but the function uses "$oSheet", so it's confusing when debugging.

2. The worksheet to hold the pivot table needs to be created before the pivot cache is created. I was getting errors using a "create pivot cache, create worksheet, create pivot table" pattern. Changing to "create worksheet, create pivot cache, create pivot table on new worksheet" was successful.

3. Passing in the name of the Excel worksheet to _ExcelPivot_CreateTable never worked. I had to use object notation like: $oWorkbook.Worksheets($pivotTableSheet) 

Edited May 18, 2022 by MrTWist

[ptrex]

@MrTWist 

Thanks for the update 

 

@SkysLastChance 

it is possible to add calculated fields for a pivot table ... the function is not present in the current UDF.

But you can easily add this in your script.

See here an example in VBA (which can be translated to AU3)

https://www.excelanytime.com/excel/index.php?option=com_content&view=article&id=152:excel-pivot-tables-insert-calculated-fields-a-calculated-items-create-formulas-using-vba&catid= 

 

[water]

I had a look at the UDF and noticed a few problems with documentation, variable naming etc.
It should be easy to update the UDF so it adheres to the rules.
I'm not sure the OP is still around as he has been offline for > 6 month. If you like I would start to brush up the UDF.
What do you think?
 

[supersonic]

@water

That would be great!

 

[water]

First q&d try. Only one function (_ExcelPivot_CacheCreate) has been brushed up.

• Added headers according to the UDF specification
• Replaced magic numbers with the constants used by MS, added links to the MS docs
• Renamed the function names to start with the object name (SCRIPT BREAKING CHANGE)
  e.g. _ExcelPivot_CreateCache became _ExcelPivot_CacheCreate
• Added sensible default values for parameters similar to the Excel UDF 
  e.g. If Range is not specified the UsedRange property is used
• Parameters now allow multiple formats
  e.g. Worksheet can be the name, the index or the worksheet object
• Added error handling so @error and @extended are set. Added documentation to the header.
  A COM error handler might still be needed. 

What do you think?
is anything missing?

ExcelPivot_FirstTry.au3

Edited May 25, 2022 by water

[supersonic]

Definitely a good way to start.

A COM error handler is mandatory - just my 2 cents.

I put all constants into a seperate AU3 in order to not collide with AutoIt's own "ExcelConstants.au3" - sooner or later...

 

 

 

Edited May 25, 2022 by supersonic

[water]

• Added a COM error handler (taken from the OutlookEX UDF)
• I will only add Pivot related constants to the UDF. As the Excel UDF gets included by the Pivot UDF I will quickly notice any double definitions 😉

[supersonic]

Great!

Perhaps this "rewrite" deserves its own topic?

 

[water]

Great idea! - Done!

Edited May 25, 2022 by water
Added the link to the new topic