Jump to content

Excel Pivot Tables UDF


sudeepjd
 Share

Recommended Posts

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 by sudeepjd
Added Further error checking and added an Example
Link to comment
Share on other sites

  • 4 weeks later...
  • 1 year later...

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.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

  • 2 months later...
  • 3 months later...

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 by MrTWist
Link to comment
Share on other sites

@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= 

 

Link to comment
Share on other sites

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?
 

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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 by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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 by supersonic
Link to comment
Share on other sites

  • 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 😉

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

Great idea! - Done!

Edited by water
Added the link to the new topic

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...