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.
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'
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 = CType(PT.PivotFields("Account"), Microsoft.Office.Interop.Excel.PivotField)
PTF.EnableMultiplePageItems() = True
PTF.CurrentPage = "620000"
Hope you understand the code.
- CAPCSheet is the sheet name in the excel.
- An Type and Account are the column names that I’m trying to change the filters
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.
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!