In order to run the below Block of VBScript code, you first need to create the following variables:
- %Path% (the file path of your excel file)
- %SheetName% (the name of the sheet you want to extract the data from)
- %SheetNameForPivotTable% (The name of the new sheet which will contain the pivot table) *No spaces in the name*
- %RowFieldName% (The name of the column to be added to the Row Field)
- %ColumnFieldName% (The name of the column to be added to the Column Field)
- %FilterFieldName% (The name of the column to be added to the Filter Field)
- %AggregationMethod% (The method of aggregation: see https://docs.microsoft.com/en-us/office/vba/api/excel.xlconsolidationfunction for more info)
- %AggregationFieldName% (The name of the column to be aggregated)
- %AggregationDisplayName% (The displayed name of the field)
- %FirstFreeRow% (first Excel row without data)
- %FirstFreeColumn% (first Excel column without data)
You can start with the"Launch Excel" Action to open the Excel, "Set Active Excel Worksheet" Action (if the excel file has multiple worksheets) and lastly the "Get First Free Column/Row from Excel Worksheet" Action.
Then follow the instructions in the VBscript code and run the code with the "Run VBScript" Action:
Set objExcel = GetObject("%Path%").Application objExcel.Application.Visible = True 'Add the path of the excel file Set xlBook1 = objExcel.WorkBooks.Open("%Path%") 'Add the Sheet name Set objData = xlBook1.Sheets("%SheetName%") Set objSheet = xlBook1.Sheets.Add(,objData) 'Edit the Name of the sheet (Do not use spaces) objSheet.Name="%SheetNameForPivotTable%" 'Add the Source data reference Const SrcData = "%SheetName%!R1C1:R%FirstFreeRow-1%C%FirstFreeColumn-1%" Const xlDatabase = 1 Const Version = 5 'Add the Destination Const Destination = "%SheetNameForPivotTable%!R3C1" 'Add the Pivot table name Const TableName = "PivotTable1" Set pvtcache = xlBook1.PivotCaches.Create(xlDatabase,SrcData,Version) Set pvtTable = pvtcache.CreatePivotTable(Destination,TableName) Const xlRowField = 1 'Add the field's name for Row Field pvtTable.pivotFields("%RowFieldName%").orientation = xlRowField Const xlColumnField = 2 'Add the field's name for Column Field pvtTable.pivotFields("%ColumnFieldName%").orientation = xlColumnField Const xlFilterField = 3 'Add the field's name for Filter pvtTable.pivotFields("%FilterFieldName%").orientation = xlFilterField 'Add the field's name and Alias for Sum Field Const xlSum = %AggregationMethod% pvtTable.AddDataField pvtTable.PivotFields("%AggregationFieldName%"), "%AggregationDisplayName%", xlSum
For more information, please see the attached image.