VBA Code - Filter using Array

[MateuszBlazej]

Hi,

i need to filter data in Excel spreadsheet. It can be dynamic number of values (or). In the vba code it should look like below: 

Worksheets("Sheet1").ListObjects("Table1").Range.AutoFilter _ Field:=1, _ Criteria1:=Array("1", "3", "Seattle", "Redmond"), _ Operator:=xlFilterValues

but in the Blue Prism Code Stage it is not accepted

ws.usedRange.AutoFilter(field:=ColumnNumber, Criteria1:=Array(Criteria1), Operator:=7)

How can I filter using array in the Blue Prism?

Page: FilterData Array
Stage: FilterData Array
Type: Error
Action: Validate
Description: Compiler error at line 8: 'Array' is a class type and cannot be used as an expression.
Repairable: No



Best,
Mateusz

------------------------------
Mateusz Blazej
RPA Developer
VELUX
Europe/Warsaw
------------------------------

[devneetmohanty07]

Hi Mateusz,

You can create a new action called 'Filter Values' by extending the 'MS Excel' VBO object which consists of other actions. Add the following input parameters to this action:

- Handle (Number) : The instance of the excel sessions held in the dictionary to establish the current excel connection.

- Workbook Name (Text) : The workbook name returned while using either 'Open Workbook' or 'Create Workbook' actions.

- Worksheet Name (Text) : The worksheet name where the operation needs to be performed.

- Table Name (Text) : The name of the table where the action needs to be performed.

- Field Index (Text) : The index of the column where filter needs to be applied starting from the position 1 to N.

- Array Values (Collection) : The collection with a single column named 'Field Values' comprising of all the filter values for the selected field index column


Prerequisite:

Add the following Namespace Import and External Reference in the Page Descriptions stage of the Initialize page:


Also, ensure that the following DLL is installed inside your Blue Prism installation folder. I have provided the same in the attached zip file:



Solution Workflow:




Now add a code stage called 'Filter Values' with the following parameters and code:




Dim wb, ws As Object
Dim excel, sheet As Object
Dim StrList As New List(Of String)

wb = GetWorkbook(Handle, Workbook_Name)
ws = GetWorksheet(Handle, Workbook_Name, Worksheet_Name)

wb.Activate()
ws.Activate()

excel = ws.Application
sheet = excel.ActiveSheet

For Each row As DataRow In Array_Values.Rows

If row("Field Values") IsNot Nothing Then
StrList.Add(row("Field Values").ToString)
End If

Next

sheet.ListObjects(Table_Name).Range.AutoFilter(Field:=Field_Index, Criteria1:=StrList.ToArray(),Operator:=Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlFilterValues)


Test Scenario:


I have the following excel file where I will be filtering the table based on few of the product names:


I will be passing the field index as '2' since I want to apply filter on second column along with the following array values collection:


Upon running the workflow, I get the following output:





------------------------------
----------------------------------
Hope it helps you out and if my solution resolves your query, then please mark it as the 'Best Answer' so that the others members in the community having similar problem statement can track the answer easily in future

Regards,
Devneet Mohanty
Intelligent Process Automation Consultant | Sr. Consultant - Automation Developer,
Wonderbotz India Pvt. Ltd.
Blue Prism Community MVP | Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com

----------------------------------
------------------------------