topic RE: Applying filter conditions in excel through BluePrism in Product Forum

Applying filter conditions in excel through BluePrism

KummariSrikanth — Tue, 11 Apr 2023 12:57:00 GMT

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

Regards,
Srikanth K

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

RE: Applying filter conditions in excel through BluePrism

Mukeshh_k — Tue, 11 Apr 2023 13:20:00 GMT

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

RE: Applying filter conditions in excel through BluePrism

KummariSrikanth — Wed, 12 Apr 2023 05:47:00 GMT

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

RE: Applying filter conditions in excel through BluePrism

Mukeshh_k — Wed, 12 Apr 2023 08:05:00 GMT

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.

RE: Applying filter conditions in excel through BluePrism

KummariSrikanth — Thu, 13 Apr 2023 06:43:00 GMT

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

RE: Applying filter conditions in excel through BluePrism

michaeloneil — Thu, 13 Apr 2023 08:31:00 GMT

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

RE: Applying filter conditions in excel through BluePrism

KummariSrikanth — Thu, 13 Apr 2023 10:53:00 GMT

Hi

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

Regards
Srikanth K

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

RE: Applying filter conditions in excel through BluePrism

michaeloneil — Thu, 13 Apr 2023 13:10:00 GMT

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

RE: Applying filter conditions in excel through BluePrism

KummariSrikanth — Thu, 13 Apr 2023 13:49:00 GMT

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

RE: Applying filter conditions in excel through BluePrism

michaeloneil — Thu, 13 Apr 2023 19:55:00 GMT

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

RE: Applying filter conditions in excel through BluePrism

plnarayana777 — Fri, 14 Apr 2023 01:50:00 GMT

Hi Srikanth

If you are looking for filter action , one action is already available in MS Excel VBO

There two more actions to apply and remove auto filter
You can leverage these actions

Regards

------------------------------
Lakshmi Narayana
------------------------------

RE: Applying filter conditions in excel through BluePrism

KummariSrikanth — Fri, 14 Apr 2023 13:00:00 GMT

Hi
Yeah copy pasted this code from website. I am looking for multiple filtering criteria,but the above code is for single filter.

Regards
Srikanth K

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

RE: Applying filter conditions in excel through BluePrism

KummariSrikanth — Mon, 17 Apr 2023 05:17:00 GMT

Hi Lakshmi Narayana,

I dont have the excel VBO for filters. I made a VBO with the above mentioned code but it is not working.

Regards,
Srikanth K

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

RE: Applying filter conditions in excel through BluePrism

plnarayana777 — Mon, 17 Apr 2023 05:35:00 GMT

Download the latest version from DX portal , it is available in it

Regards

------------------------------
Lakshmi Narayana
------------------------------