Pivot Table Filters

Help
1

Hi. Looking into Filter Pivot Table ways. Saw we have Create Pivot table & Refresh…but no filter (add columns to either Rows or Values). I thought I saw a package I can download but can’t find it anymore. Also looking into VB Code…but don’t see values in here? Can anyone please help me? Thank you so much!

(Additional- do I just use an anchor base activity and hot keys to double click the cell next to Grand Total? Any ways I can do it in the background without necessarily opening up excel? Document is pretty hefty data wise)

Sub Adding_PivotFields()
'PURPOSE: Show how to add various Pivot Fields to Pivot Table
'SOURCE: www.TheSpreadsheetGuru.com

Dim pvt As PivotTable

Set pvt = ActiveSheet.PivotTables(“PivotTable1”)

'Add item to the Report Filter
pvt.PivotFields(“Year”).Orientation = xlPageField

'Add item to the Column Labels
pvt.PivotFields(“Month”).Orientation = xlColumnField

'Add item to the Row Labels
pvt.PivotFields(“Account”).Orientation = xlRowField

'Position Item in list
pvt.PivotFields(“Year”).Position = 1

'Format Pivot Field
pvt.PivotFields(“Year”).NumberFormat = “#,##0”

'Turn on Automatic updates/calculations --like screenupdating to speed up code
pvt.ManualUpdate = False

End Sub

2

There is no Activity is available in UiPath to Filter Pivot table as of now. You can write a VBA code and execute it using the Invoke VBA activity.

3

thanks Harisha!

I’ve put the VB code, not too familiar with it, but know some from UFT. In this code (in original post above) i don’t see something for the ‘Values’ field…any idea how I can implement that as a filter? I see Rows, Columns, etc…but not Values

4

Can you provide some sample data and explain the exact requirement so that I can get a clear picture

5

Hey Harisha,

I’ve got an excel sheet like the image below. image The top row are column headers. Need to create a pivot table from this.From the pivot table, I’d like to put ‘OFF’ column in rows filter field and "NUM’ column in values filter field. I’m trying to do this in the backend instead of clicking in Excel per se. Thank you so much!

PivotedImage
PivotedImage1361×454 21.1 KB

6

is the issue resolved ? can u let me know what the solution u got?

7

For the VALUES you can include:

  • If you want count the values:

oPivotField=CType(oPivotTable.PivotFields(“name of column”),Microsoft.Office.Interop.Excel.PivotField)‘Specfiy the coulmn name’
oPivotField.Orientation=Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlDataField
oPivotField.Function=Microsoft.Office.Interop.Excel.XlConsolidationFunction.xlCount
oPivotField.Name=“Count_Of_ID”

  • If you want SUM de values:

oPivotField=CType(oPivotTable.PivotFields(“name of column”),Microsoft.Office.Interop.Excel.PivotField)‘Specfiy the coulmn name’
oPivotField.Orientation=Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlDataField
oPivotField.Function=Microsoft.Office.Interop.Excel.XlConsolidationFunction.xlSum
oPivotField.Name=“Sum_Of_ID”

Regards

8

I dont think they currently have an activity that filter excel pivot table for multiple values, but i developed a walkaround to filter for or exclude multiple values, check it out, see if it is useful
FilterPivotTableExcel.zip (35.2 KB)