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 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)

'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.