Filtering pivot tables in UiPath with diverse criteria

Help Activities
, , ,
1

Hi Team,

I have created 3 Pivot Tables (one per file) and I need to apply different filters for each Pivot Table as per the below mapping:

File Filter Fields Filter Criteria
File 1 Vendor 10027–10982 ; 11059–11142 ; 11202–17715 ; 17747–19274

| File 3 | Row Labels | Starts with “HK”, “SP”, “SW”, “000W”, “HZR0”, “PSEI”, “SCS0” |
| File 4 | Status | PENDING RECEIPTS, READY TO PAY |

I am facing issues with the following approaches:

  • Filter Pivot Table activity does not work correctly for multiple values or “Starts With” conditions.
  • Simple Excel filter is not suitable since data is already in Pivot Tables.
  • Tried using VB script (macro) via Invoke VBA, but it behaves inconsistently and sometimes throws errors like macro not enabled, field not available, or pivot item cannot be set.

What is the recommended and stable approach in UiPath (Modern Excel activities) to filter Pivot Tables with:

  • Multiple values
  • Value ranges
  • Starts With conditions
  • Different filters for different Pivot Tables

A sample workflow or example would be really helpful.

Thanks in advance.

Forum Engagement Daily Reports
2

Hi @RPA_Learner0

UiPath does not handle complex Pivot Table filters (multiple values, ranges, or “Starts With”) reliably using Filter Pivot Table or VBA. The most stable approach is to apply all filtering on the source data instead. Read the data into a DataTable, apply conditions using LINQ or Filter Data Table, write the filtered data to a helper sheet, and then refresh the Pivot Table. This works consistently and supports different filters for different files.

3

Hi @RPA_Learner0

Dont rely on complex logic to refresh pivot table.
First filter data using filter datatable activity/linq query
Then write the filtered data to a specific sheet, here refresh the pivot using linq query.
this will fully supports ranges, multiple values, and StartsWith as required in your case.

You can also add extra column in your data and check row labels , then do refrsh like
=OR(LEFT(A2,2)=“HK”,LEFT(A2,2)=“SP”,LEFT(A2,2)=“SW”)

4

Thanks Both but no sense of both solutions . I already mentioned there every approches i have tried .
If anyone can share the workflow then it’ll more helpful.

5

Hi @RPA_Learner0

Could you please share a dummy Excel file with a pivot table with those filters configured manually for us to investigate?

6

Thanks @loginerror
I just taking 1 sample and need below filters only.

Filter on Row Labels Start with “HK” “SP” “SW”, or this fixed values “000W”, “HZRO” “PSEI” “SCSO”

[3. Template.XLSX|attachment

Once this resolved by activity then pls also provide solution for file1 like folder Between range of vendors no.

7

Thank you. I now understand the requirement a bit better, but unfortunately I don’t think there is an out of the box UiPath activity that can do this right now.

The custom macro code seems like the best bet here for now. We will take this as feedback for future improvements.

8

Yes pls provide some activities to do work easily.

I tried VBA but in some files working but some file through error like below or more:
NOTE: i already enabled macro setting for all manually and in activity also…

Invoke VBA: In order for this activity to work, Trust Access to the VBA project object model must be enabled from Excel (File > Options > Trust Center > Trust Center Settings > Macro Settings > Select the Trust Access to the VBA project object model check box)