Applying a Filter in Excel with Multiple Critera

[Jean_LucFarrugi]

Dear Sir/Madam,


I have managed to create a custom Excel object which includes a filtering feature. However It works well when only a single criteria is used... I am attempting to filter only records that have "Failed" written, or that have "blank values" in the field of interest using the following code:

Dim worksheet as Object
Worksheet = GetWorkbook(handle, Nothing). ActiveSheet
worksheet.UsedRange.AutoFilter (Field:= fieldToBeFiltered,  Criteria1:=criteriaToFilter, Operator:=2, Criteria2:="=")

However when running the process showing Excel output, only the "Blank" cells are being selected to be filtered, leaving out the "Failed" rows:

How can I proceed to correctly use more than one criteria fo the filter please?

------------------------------
Jean Luc Farrugia
------------------------------

[dmma]

Hello,
Please check this documentation from Microsoft: https://docs.microsoft.com/en-us/office/vba/api/excel.range.autofilter

There is an example how to filter by several values:


Hope you will be able to update your code and make it work.

------------------------------
Kind regards,

Dmitrij Mamajev
RPA Developer
Volvo Cars
Gothenburg - Sweden
------------------------------

[EVIPUTI]

I remember someone helped me for this few months back . Please find the below code.

You can apply filters by adding a new action to the existing MS Excel VBO.
- Take any of the simple diagrams of the existing actions in the VBO
- Modify the input and output parameters as per your requirement
- Ideally it can contain only one Code stage .
- Add the below code in the Code stage
This is just a sample working code under general conditions. You will need to amend it as per your requirement.

Code:

Dim sw, dw As Object
Dim ss, ds As Object
Dim excel, sheet, varUsedRange As Object
Dim FilteredCount as Long


Try

sw = GetWorkbook(Handle, Source_Workbook)
ss = GetWorksheet(Handle, Source_Workbook, Source_Worksheet)


sw.Activate()
ss.Activate()
excel = ss.Application
sheet = excel.ActiveSheet

varUsedRange = sheet.UsedRange().address   'you can give your own Range

If sheet.AutoFilterMode Then
    sheet.AutoFilterMode = False    'Turn off filters if already applied
End If
   
sheet.range(varUsedRange).AutoFilter (Field:=1, Criteria1:=1)
'For text, use within double quotes as Criteria1:="one"

'For multiple conditions in the same column use along with AND or OR operator as per requirement
'sheet.range(varUsedRange).AutoFilter (Field:=1, Criteria1:=1, Operator:=xlOr, Criteria2:=2)
'If the Blue Prism Code stage doesn't recognise the Excel constants of xlAnd, xlOr then
'either get the constants from this link 
'https://msdn.microsoft.com/en-us/vba/excel-vba/articles/xlautofilteroperator-enumeration-excel
'OR you can write a quick macro code as shown in Post #2 here
'http://rpaforum.net/threads/blueprism-code-stage-to-paste-data-as-values-in-excel.1190/post-3686

FilteredCount = sheet.AutoFilter.Range.Columns("A").SpecialCells(12).Cells.Count - 1    '12 implies for xlCellTypeVisible


If FilteredCount > 0 then

'This means there were rows returned by the filter

End if

Success = True

Catch e As Exception
    Success = False
    Message = e.Message
Finally
    sw = Nothing
    ss = Nothing
    dw = Nothing
    ds = Nothing
    excel = Nothing
    sheet = Nothing
    varUsedRange = Nothing
End Try

------------------------------
------------------------------
Vipul Tiwari
Senior Process Simplification and Optimization Designer(Solutions Architect)
Fidelity International
------------------------------
------------------------------

[GarethPreston]

Hi Vipul,

Thank you. Shortly after I commented on this thread I was able to resolve my errors but couldn't delete my post!

Thanks,
Gareth

------------------------------
Gareth Preston
------------------------------

[BrentonWestwood]

Hello,

I recommend trying to use SQL to query the worksheet.   You can use the 'Data - OLEDB' object to create a connection to the worksheet like connecting to a database/database table and query the worksheet using the 'Get Collection' action.    If you have the drivers on the desktop that are needed, it is a 3 step process -- Set Connection, Get Collection (SQL), and Close connection.

------------------------------
Brenton Westwood
Systems Analyst
Southern Company
------------------------------

[AlexanderWilsso]

Thank you, Dmitrij! it helped me.

------------------------------
Alexander Wilssom
------------------------------