Excel Filtering

[GAVIN.RUDLING]

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

[ewilson]

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

[GeoffHirst]

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

[GAVIN.RUDLING]

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

[ewilson]

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

[GAVIN.RUDLING]

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

[ewilson · Accepted solution]

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

[ritansh.jatwani]

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

[GAVIN.RUDLING]

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

[GAVIN.RUDLING]

Hi Ritansh, Thanks for the information, Much appreciated.

Kind regards

Gavin