Hi. I have finally found the time to look into the issue.
I've modified the script a bit. See this:
Set objExcel = CreateObject("Excel.Application")
'Open the workbook
Set objWorkbook = objExcel.Workbooks.Open("%FilePath%")
'Add the Sheet name
Set objData = objWorkbook.Sheets("%SheetName%")
Set objSheet = objWorkbook.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 = objWorkbook.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
'Save changes and close Excel
objWorkbook.Save
objWorkbook.Close
objExcel.Quit
Note that this requires the Excel file to be closed when you run the script. But it also requires the first free row and column for the script to work. So, you will need to Launch Excel, then Get first free column/row from Excel worksheet, then Close Excel and then Run VBScript.
I've tested it with the following actions and it worked fine (a PivotTable was created):
You can use this to copy and paste into PAD:
SET FilePath TO $'''C:\\RPA\\Test.xlsx'''
SET SheetName TO $'''Sheet1'''
SET SheetNameForPivotTable TO $'''Pivot'''
SET RowFieldName TO $'''Test'''
SET ColumnFieldName TO $'''SomeColumnName'''
SET FilterFieldName TO $'''Test'''
SET AggregationMethod TO -4157
SET AggregationFieldName TO $'''SomeOtherColumnName'''
SET AggregationDisplayName TO $'''Sum of Something'''
Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: FilePath Visible: True ReadOnly: False Instance=> ExcelInstance
Excel.GetFirstFreeColumnRow Instance: ExcelInstance FirstFreeColumn=> FirstFreeColumn FirstFreeRow=> FirstFreeRow
Excel.CloseExcel.Close Instance: ExcelInstance
Scripting.RunVBScript.RunVBScript VBScriptCode: $'''Set objExcel = CreateObject(\"Excel.Application\")
\'Open the workbook
Set objWorkbook = objExcel.Workbooks.Open(\"%FilePath%\")
\'Add the Sheet name
Set objData = objWorkbook.Sheets(\"%SheetName%\")
Set objSheet = objWorkbook.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 = objWorkbook.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
\'Save changes and close Excel
objWorkbook.Save
objWorkbook.Close
objExcel.Quit''' ScriptOutput=> VBScriptOutput ScriptError=> ScriptError
You will obviously need to change all (or most of) the variable values and you might want to have different aggregation methods. Please see the article I shared earlier to figure out what you need.
But this at least is verified to work fine.
-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.
I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.
