How to refresh a Excel pivot table with SAP Intelligent RPA 1.0

[jleonard]

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.