Applying filter conditions in excel through BluePrism

[KummariSrikanth]

Hi,
How to apply filter conditions in excel through blue prism & get that data into collection.


Regards,
Srikanth K

------------------------------
Kummari Srikanth
------------------------------

[Mukeshh_k]

Hi @Kummari Srikanth - Refer this thread : https://community.blueprism.com/discussion/excel-font-color?ReturnUrl=%2fcontent%2fallrecentposts and just update this code with below code, pass the inputs and criteria you want to apply :


Code : 

Dim ws As Object

ws = GetWorksheet(Handle, Workbook, Worksheet)

ws.Activate()

ws.Range("A1").AutoFilter(Field:= Field, Criteria1:= Criteria)

------------------------------
Kindly up vote this as "Best Answer" if it adds value or resolves your query in anyway possible, happy to help.

Regards,

Mukesh Kumar - Senior Automation Developer

NHS, England, United Kingdom, GB
------------------------------

[KummariSrikanth]

Hi Mukesh Kumar,
After filtering, I need that filtered data into a collection. There are no outputs in the above Code.Is this possible.

Regards,
Srikanth K

------------------------------
Kummari Srikanth
------------------------------

[Mukeshh_k]

Hi Kummari Srikanth you can simply call the action get worksheet as collection from either process - or just call the page get worksheet as collection inside the action after code stage created as above, it should do for you.

------------------------------
Kindly up vote this as "Best Answer" if it adds value or resolves your query in anyway possible, happy to help.

Regards,

Mukesh Kumar - Senior Automation Developer

NHS, England, United Kingdom, GB
------------------------------

[KummariSrikanth]

Hi Mukesh Kumar,

I tried to get the filtered data into the collection by using Get worksheet as Collection Action. But it is not giving the filtered data into collection.

Regards,
Srikanth K

------------------------------
Kummari Srikanth
------------------------------

[michaeloneil]

Hi @Kummari Srikanth 

The best way to get the filtered data is after filtering do a get number of rows actions and then use get worksheet range. This should give you the filtered data.

------------------------------
Michael ONeil
Technical Lead developer
NTTData
Europe/London
------------------------------

[KummariSrikanth]

Hi

Got this Error:Exception from HRESULT: 0x800A03EC. After Executing the excel filter Code.

Regards 
Srikanth K

------------------------------
Kummari Srikanth
------------------------------

[michaeloneil]

This sounds like an error in either one of the input parameters or with the excel file. Check the filter parameter you're entering exactly matches the value in the workbook i.e. make sure there are no spaces in the text. If you can share screenshots we might be able to help you further.

------------------------------
Michael ONeil
Technical Lead developer
NTTData
Europe/London
------------------------------

[KummariSrikanth]

Hi
Inputs screenshot is provided below &
Copy pasted Below 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, Workbookname)
ss = GetWorksheet(Handle,Workbookname,Worksheetname)

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, Criteria:=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

Regards,
Srikanth K

------------------------------
Kummari Srikanth
------------------------------

[michaeloneil]

Hi Kummari

The code looks a little off to me and there are a lot of rows commented out was this intentional? also was this something you got from a website and copied to a code stage to try to use? and lastly is this to provide multiple criteria for the filter?

------------------------------
Michael ONeil
Technical Lead developer
NTTData
Europe/London
------------------------------