Technical Articles
How to refresh a Excel pivot table with SAP Intelligent RPA 1.0
Most of the automations use Excel files, the SAP Intelligent RPA 1.0 Excel library is pretty complete and powerfull. But we can’t cover everything, so I will share a solution for this customer requirement : “How to refresh a pivot table once an excel file has been updated with Intelligent RPA”
In this article I will explain how I’ve done this.
Set up the test environment :
Create an excel file :
First of all I’ve created an Excel file with two tabs.
The data tab ;
And the pivot tab :
Create a workflow :
I’ve created a simple workflow that add a value in the data sheet, like this :
Refresh the pivot table :
This function is not provided yet by the Excel library of SAP intelligent RPA. So we have to write custom code.
After a little search on the Microsoft documentation i found the function to call.
The issue here is that SAP intelligent RPA doesn’t expose the Excel objects. So I had to retrieve the application Excel object in order to call the function.
And here is the function I’ve made to refresh my pivot table :
function updatePivotTable(){
var excelApp = ctx.excel.application.getObject();
excelApp.Worksheets("Pivot").Range("A1").PivotTable.RefreshTable();
}
Here A1 is the cell here I have added the pivot table and “Pivot” is the name of the sheet.
You just have to call this function in a custom step before the Release Excel step, and the job is done.
Conclusion
Now you know how to refresh a pivot table with SAP Intelligent RPA. And with this method you can also add other function if you want.