cancel
Showing results for 
Search instead for 
Did you mean: 

Excel Filtering

GAVIN.RUDLING
Level 7
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
------------------------------
1 BEST ANSWER

Helpful Answers

@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
25334.png
Filtered w/ Autofilter Enabled and Criteria Provided
25335.png
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
------------------------------

View answer in original post

17 REPLIES 17

ewilson
Staff
Staff
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
Staff
Staff
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
------------------------------
Geoff Hirst Senior DX Engineer - Digital Exchange - EMEA SS&C Blue Prism

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

 

25325.png

 

 



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

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

 



@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
25334.png
Filtered w/ Autofilter Enabled and Criteria Provided
25335.png
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
Level 9
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
------------------------------

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

 



Hi Ritansh, Thanks for the information, Much appreciated.

 

Kind regards

Gavin