Manage Pivot Tables

The example below explains how to create and refresh a pivot table. It presents activities such as Create Pivot Table, Refresh Pivot Table. You can find these activities in the UiPath.Excel.Activities package.

This is how the automation process can be built:

1. Open Studio and create a new Process named by default Main.

   Note: Add the example.xlsx file used for retrieving data in the main folder of the project. The automation process uses this file to extract and manipulate the raw data.
2. Drag a Sequence container in the Workflow Designer.

3. Place an Excel Application Scope activity inside the body of the Sequence.

   • In the Properties panel add the path "example.xlsx" in the WorkbookPath field.
   • Select the check boxes for the AutoSave, CreateNewFile and Visible options. The robot is now allowed to create a new Excel workbook, to automatically save all the changes made to it and to open the Excel file in the foreground while performing actions on it.

4. Select the Do sequence container inside the Excel Application Scope activity.

   • Create a variable named rangeResult and of type String.

5. Drag a Create Pivot Table activity inside the Do sequence.

   • In the Properties panel, add the value L9 in the Range field, the name pivot in the TableName field, the name "SalesOrders" in the SheetName field, and the name "table" in the SourceTableName field.

6. Place a Message Box activity below the Create Pivot Table activity.

   • In the Properties panel add the value Ok in the Buttons field and add "Check the pivot table in the Excel file." in the Text field. Select the check box for the TopMost option for always having the message box displayed to the foreground.

7. Drag a Delete Range activity under the Message Box activity.

   • In the Properties panel add "A6:G16" in the Range field, add the value "SalesOrders" in the SheetName field and the value ShiftUp in the ShiftOption field.
   • Select the ShiftCells check box for shifting the cells according to the ShiftOption.

8. Place a Refresh Pivot Table activity under the Delete Range activity.

   • In the SheetName field add the name of the sheet, SalesOrders.
   • In the TableName field add the name of the table, pivot.
9. Run the example. The automation process uses the data in the example.xlsx file to create a pivot table and refreshes the initial table by deleting a range of cells.

Download example