Excel Filtering

[GavinRudling]

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

[GavinRudling]

Hi Eric, Quick question:

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

Kind regards

Gavin

[ewilson]

Hi @GavinRudling,

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

[ewilson]

Hi @GavinRudling,

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

[michaeloneil]

Hi @GavinRudling

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

[ewilson]

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

[GavinRudling]

Hi Guys this is perfect , Thank you so much.

Saved us from using macro code. Much appreciated.

Regards

Gavin

[GavinRudling]

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

Kind regards

Gavin

[GavinRudling]

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

Regards

Gavin