How to prepare the Pivot table in Power automate desktop flow?

[Ahmedmuz]

Hi Everyone,

How to prepare the Pivot table, Add filter, columns, rows, value & also copy paste the pivot table into the new sheet  in Power automate desktop flow please help ?

[Agnius · Accepted solution]

Power Automate Desktop does not have any native actions for building or modifying Pivot tables in Excel. What you can do, however, is use the Run VBscript action to call a VBscript that creates/modifies your Pivot table.

See this page for reference. This is part of the WinAutomation (predecessor of Power Automate Desktop) documentation, but the Run VBscript action works the same way and the script itself is valid.

https://learn.microsoft.com/en-us/previous-versions/troubleshoot/winautomation/process-development-t...

It has a pretty detailed explanation, and also provides references for more details. 

-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution.
If you like my response, please give it a Thumbs Up.

If you are interested in Power Automate, you might want to follow me on LinkedIn at https://www.linkedin.com/in/agnius-bartninkas/

[Ahmedmuz]

its looks very tricky and confusion here,,, 😞

[Agnius]

The explanation is quite detailed, so I don't see why it is confusing.

There is no other way to do it, however, apart from, maybe, trying to do a script in PowerShell or Python, if you prefer those over VBscript. In any case, you will need scripting to create or modify an Excel Pivot table using Power Automate Desktop.

-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution.
If you like my response, please give it a Thumbs Up.

If you are interested in Power Automate, you might want to follow me on LinkedIn at https://www.linkedin.com/in/agnius-bartninkas/

[Ahmedmuz]

Hi Agnius,

By using above VBScript can you show one example n snap shot it will help me how to edit in code or in variables please for pivot ?

[Ahmedmuz]

Hi I tried but its not creatting the pivot table please help ,, Flow is running but its not creating pivot table where i am doing wrong please help  below are snap shots

[Agnius]

Actually, I've just noticed that the script is a bit off. It initializes the object slightly incorrectly. Try this instead:

Set objExcel = GetObject(, "Excel.Application")

'Activate the workbook
Set objWorkbook = objExcel.Workbooks("%FileName%").Activate

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

This will run correctly when you already have the worksheet open - it will attach to the worksheet, instead of trying to open it again.

Your flow is correct, except you no longer need the path to the file for the script, but you need the name of the file in %FileName% for the script to be able to attach to it.

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

[Ahmedmuz]

Hi Agnius,

As per your instructions, I have removed the path variable and created one with Filename. I have also copied and pasted the provided code. However, I am encountering an issue as the pivot table is not being created in Excel. I have attached a video of the Excel process for your reference. Please provide assistance in resolving this matter.

Please let me know do i need to do any changes in code as i need pivot to get selected from sheet1!$A$1:$AC$4.

[Ahmedmuz]

@Agnius Request you to please help on the above - i have attached a small video and also what went wrong please help asap it will be means a lot.

Best Regards 

Muzamil Ahmed

[Ahmedmuz]

@Agnius 

but you need the name of the file in %FileName% for the script to be able to attach to it.

What i have to pass in the Filename Variable - Please help ??

[Agnius]

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.

[Ahmedmuz]

Hi Agnius,

Thank you so much for your help it means a lot:):):). But have couple issues as mentioned below.

1.I need to open multiple Excel files, located in a specific folder, one by one, and create a PivotTable in each of them. I am seeking guidance on how to efficiently tackle this task in a systematic manner In Power automate desktop flow.

2.Once the PivotTable is created in each Excel file, I have additional tasks that need to be performed within Excel. However, if Excel is closed after creating the PivotTable, I am uncertain how to proceed with these subsequent actions.
How to tackle this two issues in Power automate desktop please help 

Best Regards 

Muzamil Ahmed

[Agnius · Accepted solution]

1. Use Get files in folder to get the files, then use For each on the files and pass the file path of the current item to the script. Move the Set variable actions, as well as the Excel actions and the Run VBScript action inside the loop.

2. Simply use the Launch Excel action again to run it again and do whatever you need to do with it.

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

[Ahmedmuz]

Thanks agnius,

Sorry for the confusion,,
If the VBScript run with already existing open excel file it will be solved all the issue as alreay im in get in file folders and other filtration,

If you could help with a Vbscript which works with which is already open it helps and solved all the issues

As in VBScript we have give the excel file path what to give thier as well ?

Best Regards 

Muzamil Ahmed

[Agnius · Accepted solution]

I've just tested and to my minor surprise, the Workbooks.Open() method actually works when the workbook is already open in Excel.

So, you can use this script in the same flow and it will actually work with a file that is already open. No need to change any variables from my suggestion above. The only thing I changed is the first line, that gets an existing Excel object instead of creating a new one.

Set objExcel = GetObject(,"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

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

[Ahmedmuz]

Its working fine now:)

[Agnius]

Well, I do get values when I run the script. Have you actually modified the names of the columns properly in your variables? Can you show the entire flow?

[Ahmedmuz · Accepted solution]

@Agnius, Its working fine thanks a lot,,,

[Agnius]

If it is, feel free to mark the appropriate reply as the accepted solution to the topic.

[elena3]

Hello,
I wanted to follow use the same power automate flow, but when I follow the link it is not visible anymore because it says that the page "has retired"...
could you please post a screenshot of the solution maybe, if you still have it?
Thank you very much!