#include-once
#include <Excel.au3>

; #INDEX# =======================================================================================================================
; Title .........: Create Excel Pivot Tables and Charts
; AutoIt Version : 3.3.2 or later
; UDF Version ...: 0.4 (based on version 0.3 written by sudeepjd, see: https://www.autoitscript.com/forum/topic/204034-excel-pivot-tables-udf)
; Language ......: English
; Description ...: A collection of functions to create and update Pivot Tables and Pivot Charts in Excel
; Author(s) .....: sudeepjd
; Modified.......: water
; Contributors ..:
; Resources .....:
; ===============================================================================================================================

#Region #VARIABLES#
; #VARIABLES# ===================================================================================================================
; Global and local variables used by this UDF
; ===============================================================================================================================
#EndRegion #VARIABLES#

#Region #CONSTANTS#
; #CONSTANTS# ===================================================================================================================
; Enumerations: https://docs.microsoft.com/en-us/office/vba/api/excel(enumerations)

; XlPivotTableSourceType enumeration. Specifies the source of the report data
Global Const $xlConsolidation = 3 ; Multiple consolidation ranges
Global Const $xlDatabase = 1 ; Microsoft Excel list or database
Global Const $xlExternal = 2 ; Data from another application
Global Const $xlPivotTable = -4148 ; Same source as another PivotTable report
Global Const $xlScenario = 4 ; Data is based on scenarios created using the Scenario Manager
; ===============================================================================================================================
#EndRegion #CONSTANTS#

; #CURRENT# =====================================================================================================================
; _ExcelPivot_CacheCreate
; _ExcelPivot_ChartAdd
; _ExcelPivot_FieldAdd
; _ExcelPivot_FilterAdd
; _ExcelPivot_FilterClear
; _ExcelPivot_RangeGet
; _ExcelPivot_TableCreate
; _ExcelPivot_TableRefresh
; ===============================================================================================================================

; #INTERNAL_USE_ONLY# ===========================================================================================================
; Internal functions used by this UDF
; ===============================================================================================================================

; #FUNCTION# ====================================================================================================================
; Name...........: _ExcelPivot_CacheCreate
; Description ...: Creates the Cache for the Excel Pivot
; Syntax.........: _ExcelPivot_CacheCreate($oWorkbook[, $vWorksheet = Default[, $sRange = Default]])
; Parameters ....: $oWorkbook  - The Excel Workbook object (returned by _Excel_BookOpen, _Excel_BookAttach or _Excel_BookNew)
;                  $vWorksheet - [optional} The sheet that holds the data. Specify as object, name or number (default = keyword Default = active Worksheet)
;                  $vRange     - [optional} Range of data which needs to be pivoted. Specify as Range object or A1 range.
;                                Use keyword Default to process all used cells (default)
; Return values .: Success - Handle to the Pivot Cache
;                  Failure - Returns 0 and sets @error:
;                  |1 - $oWorkbook is not an object or not a workbook object
;                  |2 - $vWorksheet name or index are invalid or $vWorksheet is not a worksheet object. @extended is set to the COM error code
;                  |3 - $vRange is invalid. @extended is set to the COM error code
;                  |4 - Error creating the Pivot Cache. @extended is set to the COM error code
; Author ........: sudeepjd
; Modified ......: water
; Remarks .......: The reason for keeping the cache creation in a separate function is that the same cache can be used
;                  across pivot tables, if more than one are required.
; Related .......:
; Link ..........:
; Example .......: No
;============================================================================================
Func _ExcelPivot_CacheCreate($oWorkbook, $vWorksheet = Default, $vRange = Default)
	Local $oCache
	If Not IsObj($oWorkbook) Or ObjName($oWorkbook, 1) <> "_Workbook" Then Return SetError(1, 0, 0)
    ; Get the Worksheet object
	If Not IsObj($vWorksheet) Then
		If $vWorksheet = Default Then
			$vWorksheet = $oWorkbook.ActiveSheet
		Else
			$vWorksheet = $oWorkbook.WorkSheets.Item($vWorksheet)
		EndIf
	EndIf
	If @error Or Not IsObj($vWorksheet) Then Return SetError(2, @error, 0)
    ; Define Data Range
	If $vRange = Default Then
		$vRange = $vWorksheet.Usedrange
	ElseIf Not IsObj($vRange) Then
		$vRange = $vWorksheet.Range($vRange)
		If @error Or Not IsObj($vRange) Then Return SetError(3, @error, 0)
	EndIf
	; Define Pivot Cache
    $oCache = $oWorkbook.PivotCaches.Create(1, $vRange)
	If @error Then Return SetError(4, @error, 0)
    Return $oCache
EndFunc