Sort data in Pivot table

[marco.roensch]

Hey Guys,

I’ve developed a step which creats the following pivot table:

I’d like to have the second column sorted but it sorts the first column automatically.
In Excel it would look like this:

My question is, how can I sort or more precisely create the pivot table with the amounts sortet (descending) in UiPath?

Thank you guys!

Regards
Marco

[ashokkarale]

@marco.roensch,

No out of the box solution as of now but you can use Invoke VBA script for this.

Try this script.

Sorting Fields and Values in Pivot Table

Thanks,
Ashok

[mukesh.singh]

UiPath doesn’t have a direct activity to sort within a pivot table in Excel. However, there are two workarounds you can consider to achieve your desired sorting but you can check in UiPath Marketplace:

Got code from internet but @marco.roensch you can try this

1. Leverage VBA Code (Invoke Code Activity):

This approach utilizes VBA code to interact with the Excel object model and sort the pivot table. Here’s a basic example:

Sub SortPivotTable()

  ActiveSheet.PivotTables(1).PivotFields("**Field to Sort**") _
      .AutoSort xlDescending, "**Value Field Name**"

End Sub

Explanation:

• Replace “Field to Sort” with the actual name of the column you want to sort (e.g., “Product”).
• Replace “Value Field Name” with the name of the value field you want to sort by (e.g., “Sales”).
• Use the “Invoke Code” activity in UiPath and paste this VBA code into it.

2. Sort After Exporting Data (Sort Data Table Activity):

1. Export Data: Use the “Excel Application Scope” and “Read Range” activities to extract the data from the pivot table into a UiPath DataTable.
2. Sort Data: Utilize the “Sort Data Table” activity. Specify the column to sort by (the second column) and the sort order (descending).
3. Write Back (Optional): If needed, use the “Excel Application Scope” and “Write Range” activities to write the sorted data back to a new location in your Excel sheet.

Choosing the Right Approach:

• If you prefer a non-VBA solution and don’t mind an extra step of writing data to a separate table, use method 2.
• If you’re comfortable with VBA and want a more streamlined approach, use method 1.

Remember to adjust the code snippets and activity properties based on your specific pivot table structure and desired outcome.

[marco.roensch]

Thanks guys for your explanation!
I never used VBA Code before and have to learn it first!