Unable to filter pivot table

I have an excel sheet where there is a pivot. I would want to filter a cells value. Hence I used filter table. Gave the table name as PivotTable as mentioned in it. On executing it throws error as follows:
Source: Filter Table

Message: The table does not exist.

Exception Type: UiPath.Excel.ExcelException

An ExceptionDetail, likely created by IncludeExceptionDetailInFaults=true, whose value is:
UiPath.Excel.ExcelException: The table does not exist. ----> System.Runtime.InteropServices.COMException: Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))
at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32 aWrapperTypes, MessageData& msgData)
at Microsoft.Office.Interop.Excel.ListObjects.get__Default(Object Index)
at UiPath.Excel.WorkbookApplication.GetTable(String tableName)
— End of inner ExceptionDetail stack trace —
at UiPath.Excel.Activities.ExcelInteropActivity`1.EndExecute(AsyncCodeActivityContext context, IAsyncResult result)
at System.Activities.AsyncCodeActivity.System.Activities.IAsyncCodeActivity.FinishExecution(AsyncCodeActivityContext context, IAsyncResult result)
at System.Activities.AsyncCodeActivity.CompleteAsyncCodeActivityData.CompleteAsyncCodeActivityWorkItem.Execute(ActivityExecutor executor, BookmarkManager bookmarkManager)

Appreciate if someone could help me to sort out the above mentioned issue.

Hi @Thushar_Zacharia,

But i don’t hope we can filter a Pivot Table using the activities. Better to write VB code for filtering the Pivot table.

Thanks Hareesh…just in case if you get an idea please do let me know.

Do you have an idea of the VBA code that I can write here …I wanted two values in a cell to be filtered from a list of items.
I have no much idea on how to use VBA code with existing Uipath flow I created to run .

Here is the entire code that you can use in the invoke code activity to open an excel, do all the operations that you need to perform

Note** :This code works for existing pivot tables in a sheet. To create a new pivot table, the code will be different. Let me know if you want that as well

  Dim excel As Microsoft.Office.Interop.Excel.Application
    Dim wb As Microsoft.Office.Interop.Excel.Workbook
    Dim ws As Microsoft.Office.Interop.Excel.Worksheet
      ws = CType(wb.Sheets("CAPCSheet"), Microsoft.Office.Interop.Excel.Worksheet) 'select            a sheet and activiates'

    ws.Activate()



    p_ws = CType(wb.Sheets._Default("CAPCSheet"), Microsoft.Office.Interop.Excel.Worksheet)

    p_rng = CType(p_ws.Cells(1, 1), Microsoft.Office.Interop.Excel.Range)
    Dim PT As Microsoft.Office.Interop.Excel.PivotTable
    Dim PTF As Microsoft.Office.Interop.Excel.PivotField
    Dim PTN As Object = ws.PivotTables(1)

    ws = CType(wb.Sheets("CAPCSheet"), Microsoft.Office.Interop.Excel.Worksheet)
    PT = CType(ws.PivotTables(CType(PTN, Microsoft.Office.Interop.Excel.PivotTable).Name), Microsoft.Office.Interop.Excel.PivotTable)'

    PTF = CType(PT.PivotFields("An Type"), Microsoft.Office.Interop.Excel.PivotField) 'Select column name that you want to change the filter
    PTF.ClearAllFilters()
    PTF.ClearValueFilters()


    PTF = CType(PT.PivotFields("Account"), Microsoft.Office.Interop.Excel.PivotField)
    PTF.ClearAllFilters()
    PTF.ClearValueFilters()
    PTF.EnableMultiplePageItems() = True
    PTF.CurrentPage = "620000"

Hope you understand the code.

  1. CAPCSheet is the sheet name in the excel.
  2. An Type and Account are the column names that I’m trying to change the filters

Hi Hareesh,

Thank you so much for taking time to respond. I am not very sure on the code but will try to understand and use it.

Between i wanted multiple selection for one of the cells. A sample of the pivot table has been attached.
Scenario is for check i have multiple items that i need to filter. I hope this code would work.

image