Create an excel pivot table from multiple sheet

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

@Ahmad_Rais ,

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:

Steps to Create a Pivot Table from Multiple Sheets in UiPath

  1. Read Data from Multiple Sheets:

    • Use the Excel Application Scope activity to open your Excel file.
    • For each sheet, use the 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
    
  2. Merge DataTables (if they are similar):

    • Use the 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.
    • Alternatively, if the sheets do not share the same structure but you still want to create a pivot, you may need to handle each DataTable independently based on their specific structure.

    Example:

    Merge DataTable (DataTable1, DataTable2, Output: CombinedDataTable)
    
  3. Write the Combined Data Table to a New Sheet:

    • After merging, use the Write Range activity to output the combined DataTable into a new sheet or a new workbook where your pivot table will be created.
  4. Create Pivot Table:

    • Use the Excel Application Scope again to open the sheet where you have written the combined data.
    • Use the Create Pivot Table activity (you may need to install an Excel package that includes this functionality, such as UiPath.Excel.Activities).
    • Specify the range of the data table you created and the destination for your pivot table.

    Example:

    Create Pivot Table
        Source: (Range of CombinedDataTable)
        Destination: (Cell Location for Pivot Table)
    
  5. Set Pivot Table Fields:

    • Use the pivot table setting activities (like Add Pivot Field, Add Pivot Data, etc.) to define what fields will act as Rows, Columns, and Values in your pivot table.
  6. Handle Updates:

    • If you want to ensure the pivot table updates automatically whenever the sheets are updated, you may need to ensure that you re-run this workflow every time there’s an update. This could be automated to run on a schedule using UiPath Orchestrator or any trigger you decide.

Example UiPath Workflow Structure

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

Notes:

  • To refresh the pivot table after updates, you may also need to add a step to invoke Refresh on the pivot table.
  • Depending on your Excel version, some activities might be slightly different, so ensure you are using the compatible version of UiPath activities for Excel.

@Ahmad_Rais

Can you show a sample

Also fid you try pivot table activities?

Cheers

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.