topic RE: Excel Filtering in Product Forum

Excel Filtering

GAVIN.RUDLING — Wed, 07 Dec 2022 11:28:00 GMT

Good Day Team

I am using the new Excel VBO I got off the Digital Exchange and trying to use the Apply filter Action.
I had to ensure that the Microsoft.Interopt.Excel file was in place, but when using the action I get an instant error of (Exception from HRESULT: 0x800A03EC)
Any ideas or suggestions?

Kind regards
Gavin

------------------------------
Gavin Rudling
Digital Consultant & Developer
Cog3nt
Africa/Johannesburg
0813906789
------------------------------

RE: Excel Filtering

ewilson — Wed, 07 Dec 2022 13:13:00 GMT

Hi @GAVIN.RUDLING,

When you say you get an "instant error" you mean calling the action results in the exception, correct?

Exception 0x800A03EC is a NAME_NOT_FOUND error I believe. What is the filter that you're trying to apply,

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

RE: Excel Filtering

GeoffHirst — Wed, 07 Dec 2022 13:38:00 GMT

Hi Gavin,

Thank you for being a SSC Blue Prism customer and for using Blue Prism.

Just a quick scan for the code and this also shows up on SO, as being an outcome when an xls worksheet is opened that is large, as in >65535 rows. The detail of the error is here https://stackoverflow.com/questions/7099770/hresult-0x800a03ec-on-worksheet-range

Please come back to us if you are still having issues with this.

regards

------------------------------
Geoff Hirst
Senior DX Engineer - Digital Exchange - EMEA
SS&C Blue Prism
------------------------------

RE: Excel Filtering

GAVIN.RUDLING — Wed, 07 Dec 2022 15:09:00 GMT

Hi Geoff / Eric

Thanks for the feedback.

The original file contains just over 54 000 lines of data.

We are using the Apply Filter Action, where we provide the filter column ID. Example column B and the ID used is 2

Then we are providing the string value we looking for. Example "Test", I have also tried "=Test" just in case. I left the bottom 3 inputs blank.

The stage before this I activate the worksheet and apply an AutoFit function just to validate visually that the VBO is working and that I am on the correct sheet.

I also tried a smaller file of just 110 lines and get the same error.

Using the code in the VBA module in the excel seems to work, but I cannot get it to work from the VBO for some reason. (Probably one of those "ID - 10 - T" type errors) 😊

Kind regards

Gavin

RE: Excel Filtering

ewilson — Wed, 07 Dec 2022 16:04:00 GMT

Ah, just realized this is a custom VBO and there's no Apply Filter action in the standard VBO. 😂 Can you share the code of the specific action. My guess is that you need to enter a value for the Operator Logic input, but without seeing the backend I can't be 100% certain.

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

RE: Excel Filtering

GAVIN.RUDLING — Wed, 07 Dec 2022 17:41:00 GMT

Hi Eric, Yes, man sorry I forgot to mention that it was the Custom VBO

The commented line of code (Line 7) was just some hard coding I put in myself to see if I got a different response.

The rest was already there. I am not sure where this Action came from originally.

Thanks a mill....

Dim ws as Object

Try

ws = GetWorkbook(Handle, Nothing).ActiveSheet

ws.UsedRange.AutoFilter (Field:= Filter_Column_Id, Criteria1 := First_Criteria, Operator:= Operator_Logic ,Critera2:= Second_Criteria, VisibleDropDown:= Show_DropDown)

'ws.UsedRange.AutoFilter (Field:=2, Criterial:="Test" )

Success = True

Catch e as Exception

Success = False

Message = e.Message

Finally

ws = Nothing

End Try

RE: Excel Filtering

ewilson — Wed, 07 Dec 2022 18:23:00 GMT

@GAVIN.RUDLING,

I was mistaken. We do have a filter capability in the standard VBO. It's just broken up across a couple different actions. In our case you must first turn on the Autofilter feature for the Range that you're interested in. After that, you can apply the filter criteria. Here are some screenshots of a quick test. I'm filtering on my name.

Unfiltered w/ Autofilter Disabled

Filtered w/ Autofilter Enabled and Criteria Provided

With your custom VBO, have you enabled autofiltering before trying to apply the criteria?

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

RE: Excel Filtering

ritansh.jatwani — Thu, 08 Dec 2022 07:50:00 GMT

Hi @GAVIN.RUDLING,

You can refer the below video for applying the filter in excel using Blue Prism:

https://www.youtube.com/watch?v=GiQgvUhbFM0&t=62s

------------------------------
Ritansh Jatwani
Manager
Deloitte
Gurgaon
*If you find this post helpful mark it as best answer
------------------------------

RE: Excel Filtering

GAVIN.RUDLING — Thu, 08 Dec 2022 08:38:00 GMT

Thanks Eric, I found the correct VBO and it is working fine. I must have found someone's Work In Progress VBO. Very different from this one.

Kind Regards

Gavin

RE: Excel Filtering

GAVIN.RUDLING — Thu, 08 Dec 2022 08:53:00 GMT

Hi Ritansh, Thanks for the information, Much appreciated.

Kind regards

Gavin

RE: Excel Filtering

GAVIN.RUDLING — Thu, 08 Dec 2022 16:22:00 GMT

Hi Eric, Quick question:

Are you able to filter multiple items in a single column at all?

Kind regards

Gavin

RE: Excel Filtering

ewilson — Fri, 09 Dec 2022 15:26:00 GMT

Hi @GAVIN.RUDLING,

Looking at the code it seems only one criteria is supported at the moment. According to Microsoft's documentation for the Autofilter method up to two criteria can be specified. We can extend the action of the VBO to allow a second criteria to passed in.

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

RE: Excel Filtering

ewilson — Fri, 09 Dec 2022 16:54:00 GMT

Hi @GAVIN.RUDLING,

FYI - We've uploaded version 10.0.3 of the VBO to the DX. The Add Criteria to Autofilter action now supports 2 criteria.

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

RE: Excel Filtering

michaeloneil — Fri, 09 Dec 2022 22:45:00 GMT

Hi @GAVIN.RUDLING

I see @ewilson has updated the vbo to allow 2 criteria on the vbo action but if you need multiples on a single column this is how I currently do it.

Inputs - handle, workbookname, worksheetname, columnnumber, range (this should be the full number of rows to apply the filter on), criteria string ( this is each filter criteria separated by a comma e.g. "criteria1,criteria2,criteria3,criteria4"

Outputs - success, message
------------------------------------------------------------------
Dim ws as Object, strList as Object

ws = GetWorksheet(handle, workbookname, worksheetname)

Try

If (criteriastring<>"") Then
strList = Split(criteriastring,",",-1)
'ColNum = ws.Range(range).Find(columnname).Column
ws.Range(range).AutoFilter(Field:=columnnumber, Criteria1:= strList, Operator:=7)
success = True
Else
success = False
message = "CriteriaString should not be blank"

End If

Catch ex as exception

success = False
message = ex.Message

End Try

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

RE: Excel Filtering

ewilson — Sat, 10 Dec 2022 00:14:00 GMT

Excellent @Michael ONeil! Great to know you can just build a single string for this. 😉

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

RE: Excel Filtering

GAVIN.RUDLING — Sat, 10 Dec 2022 06:06:00 GMT

Hi Guys this is perfect , Thank you so much.

Saved us from using macro code. Much appreciated.

Regards

Gavin

RE: Excel Filtering

GAVIN.RUDLING — Sat, 10 Dec 2022 06:41:00 GMT

Thanks Michael, this is great exactly what I was looking for. Much appreciated

Kind regards

Gavin

RE: Excel Filtering

GAVIN.RUDLING — Mon, 12 Dec 2022 05:21:00 GMT

Thank you very much Eric for the great support. Very much appreciated to you and the team.

Regards

Gavin