Hi can some one give me example on creating a pivot table from multiple sheet.
i want the result to be like manual pivot table in excel where if any values from the sheet is updated then the pivot table will also be updated
thanks
Hi can some one give me example on creating a pivot table from multiple sheet.
i want the result to be like manual pivot table in excel where if any values from the sheet is updated then the pivot table will also be updated
thanks
This may helps you:
Creating a pivot table from multiple sheets in Excel using UiPath involves a few steps. You would begin by consolidating the data from your sheets and then utilize UiPath to create a pivot table. Below are the detailed steps you would typically follow to achieve this:
Read Data from Multiple Sheets:
Excel Application Scope
activity to open your Excel file.Read Range
activity to read the data and store it in a DataTable variable. You might want to create a list of DataTable variables or merge them into a single DataTable if they share the same structure.Example:
Excel Application Scope
Read Range (Sheet1) → DataTable1
Read Range (Sheet2) → DataTable2
Read Range (Sheet3) → DataTable3
Merge DataTables (if they are similar):
Merge DataTable
activity to combine the DataTables into one. If the columns match, this will allow you to create a uniform DataTable for the pivot table.Example:
Merge DataTable (DataTable1, DataTable2, Output: CombinedDataTable)
Write the Combined Data Table to a New Sheet:
Write Range
activity to output the combined DataTable into a new sheet or a new workbook where your pivot table will be created.Create Pivot Table:
Excel Application Scope
again to open the sheet where you have written the combined data.Create Pivot Table
activity (you may need to install an Excel package that includes this functionality, such as UiPath.Excel.Activities
).Example:
Create Pivot Table
Source: (Range of CombinedDataTable)
Destination: (Cell Location for Pivot Table)
Set Pivot Table Fields:
Add Pivot Field
, Add Pivot Data
, etc.) to define what fields will act as Rows, Columns, and Values in your pivot table.Handle Updates:
Here’s a simple pseudo-structure for clarity:
1. Excel Application Scope (your file)
a. Read Range (Sheet1) → DataTable1
b. Read Range (Sheet2) → DataTable2
c. Read Range (Sheet3) → DataTable3
2. Merge DataTable (DataTable1, DataTable2, Output: CombinedDataTable)
3. Write Range (CombinedDataTable, NewSheet)
4. Excel Application Scope (your file)
a. Create Pivot Table (Source: CombinedDataTable, Destination: PivotTableLocation)
b. ...
Refresh
on the pivot table.I have found the issue to easly automate it without too much complexity,
we can use UiPath to state the range of excel as a table we want to process
then we use power query to create a summary of all the sheets
then we crete a template of pivot to read from this summary sheet
this is good solution but if we use data table then the data wont be able to be updated manually by user after that
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.