Unable to filter pivot table

Learn Academy Feedback
#1

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.

#2

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.

#3

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

#4

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 .

#5

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
#6

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

#7

@HareeshMR

I am trying to filter a pivot table, but I am unfamiliar with code and struggling to get this to work. My scenario is somewhat similar, except that I need to refresh a pivot after pasting my new data in, and in each of the “Shop?” and “New Hire?” filters, check to see if a value exists, and if not, select blank. My options in each of these filters would be Yes or No, but they aren’t guaranteed to exist within the new data I pasted.

Can you please advise on how to use code to do this type of scenario? Thank you!

image