Filter Excel Data By Specific Criteria

qqq

New Member
Perhaps array doesn't support a not equal clause yet. May be you can do some research and see if you find a code for not equal to array. If not you may need to work out a logic based on your data. Perhaps something like taking the sheet into a collection and then check by adding multiple AND separated not equal to filtering condition on the collection. If that works out then you can dump the collection back to the sheet.

anyone have a solution for this?
I need to filter a column excluding blanks and "not found"
 

CD58

New Member
VJR is it possible to set the Criteria Items and the Fields into a Data Item or do we have to rewrite the code and how do we have to do this im new to this so if you can help please ...
 

VJR

Well-Known Member
anyone have a solution for this?
I need to filter a column excluding blanks and "not found"
For Non-blank cells use the below criteria followed by another criteria on "not found"
Criteria1:="<>"
Make sure to give the correct AND or OR operator in between the above two criteria as per your requirement.

1 is for AND and 2 is for OR

'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
'get the constants from this link
'https://msdn.microsoft.com/en-us/vba/excel-vba/articles/xlautofilteroperator-enumeration-excel
 
  • Like
Reactions: qqq

VJR

Well-Known Member
VJR is it possible to set the Criteria Items and the Fields into a Data Item or do we have to rewrite the code and how do we have to do this im new to this so if you can help please ...
The code as mentioned in my comments is just a sample working code under general conditions. You will need to amend it as per your requirement.
VBOs have to be customised to be made re-usable in other processes. So no need to rewrite the code and only make modifications to it.

Yes you can store the criteria in a data item, pass it from the Process to the Object to the Code stage as parameters and then use it in the line of code where the criteria is used. It can either be a Number or a Text or any other type of data item. On searching you will find some code in the forum with screenshots where parameters are passed and used in the code.
 

abdelhakim

New Member
The code as mentioned in my comments is just a sample working code under general conditions. You will need to amend it as per your requirement.
VBOs have to be customised to be made re-usable in other processes. So no need to rewrite the code and only make modifications to it.

Yes you can store the criteria in a data item, pass it from the Process to the Object to the Code stage as parameters and then use it in the line of code where the criteria is used. It can either be a Number or a Text or any other type of data item. On searching you will find some code in the forum with screenshots where parameters are passed and used in the code.

VJR please i have probleme here, i did all what you said , and i fix all bugs , and i apply the project with a the same exemple (same Excel) but it gave me a probleme , i have this error ==> (The given key was missing from the dictionary. )
 

Attachments

  • error.PNG
    70.8 KB · Views: 73

abdelhakim

New Member
Hi viraj,

You can apply filters by adding a new action to the existing MS Excel VBO.
- Take any of the simple diagrams of the existing actions in the VBO
- Modify the input and output parameters as per your requirement
- Ideally it can contain only one Code stage - Refer 1.jpg
- Add the below code in the Code stage
This is just a sample working code under general conditions. You will need to amend it as per your requirement.

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, Source_Workbook)
ss = GetWorksheet(Handle, Source_Workbook, Source_Worksheet)


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, Criteria1:=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

Input before running the object:
View attachment 572


Output after running the object:
View attachment 573[/Qi

i find a problem ( The given key was missing from the dictionary.)
 

VJR

Well-Known Member
Hi abdelhakim.

The dictionary key error generally appears when the parameters, either input or output or both are not correctly generated from its corresponding actions for eg; handle from Create Instance Or Workbook Name data item Or sheet name or incorrect path. Also, anything else like if the parameters are not passed correctly eg; handle is created for one workbook but passed for another, if Range to be passed should be "A1:B10" and it is passed as "A1;B10", (with a semicolon instead of colon) also creates the issue. Something is not matching between the Excel and the parameters or values used in the code. Check for issues like the above and that will resolve them.
 

akram6371

New Member
VJ - Picked up and tested your code this morning and found it was really easy to use and understand. Thanks for the comments (especially around the xlAutoFitlerOperator constants. I was able to take what you started and do exactly what I needed. This was a huge help!

Thanks!

Adam
 

rejmathew

New Member
how do we use the autofilter when have 3 items to filter

Is it correct?
sheet.range(varUsedRange).AutoFilter (Field:=1, Criteria1:=1, Operator:=xlOr, Criteria2:=2,Operator:=xlOr, Criteria3:=3)
 

Nici BP

New Member
Hello VJR,

thank you for your great help!

I try to implement a filter where a text field can be "xxx" OR "yyy" OR "zzz", but I do not know what to enter as Input/Output Parameters and how to set up exception, success and message.
Do you have an idea?

I really appreciate your help.
 

LuizFelipeSilva

New Member
Hi viraj,

You can apply filters by adding a new action to the existing MS Excel VBO.
- Take any of the simple diagrams of the existing actions in the VBO
- Modify the input and output parameters as per your requirement
- Ideally it can contain only one Code stage - Refer 1.jpg
- Add the below code in the Code stage
This is just a sample working code under general conditions. You will need to amend it as per your requirement.

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, Source_Workbook)
ss = GetWorksheet(Handle, Source_Workbook, Source_Worksheet)


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, Criteria1:=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

Input before running the object:
View attachment 572


Output after running the object:
View attachment 573

Thanks!! Help a lot!
 

AHUJABHARAT

New Member
Hi I'm getting this error while copying this code and creating a Filter

The Business Object MS Excel VBO does not support the action 'Filter data'
 

Vinutha

New Member
Hi viraj,

You can apply filters by adding a new action to the existing MS Excel VBO.
- Take any of the simple diagrams of the existing actions in the VBO
- Modify the input and output parameters as per your requirement
- Ideally it can contain only one Code stage - Refer 1.jpg
- Add the below code in the Code stage
This is just a sample working code under general conditions. You will need to amend it as per your requirement.

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, Source_Workbook)
ss = GetWorksheet(Handle, Source_Workbook, Source_Worksheet)


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, Criteria1:=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

Input before running the object:
View attachment 572


Output after running the object:
View attachment 573

Hi @VJR ,

I need to filter colm A with values greater than / equal to 1. I used the above code and changed criteria as Criteria1:=">=1" and code check shows no error. However when i run the process, it fails in code stage saying given key not part of dictionary, I have already added inputs for handle, Workbook and sheet also. Could you please help what could be the cause for key issue. Thanks
 

Attachments

  • inputs to code.PNG
    5.1 KB · Views: 85
  • Exception.PNG
    20 KB · Views: 65
Last edited:

barca00

New Member
So I use your code and use for Criteria1=">10.06.2019" for the date.

And in Excel I can see the Filter is there, but it shows no Result first when I click in Excel on Filter and then go to custom Filter and open it, there I see my ">10.06.2019" and then click OK, first then the data is shown.

Do I have to reactivate Autofilter or what is the issue to that kind of behaviour.
 

Cher

New Member
Hi, I tried using the given filter code (copied the code as is) I am getting an error: "Internal : Could not execute code stage: Parameter count mismatch."Please suggest what is causing this and how to fix it? My input file is similar to what VJR had at the beginning (with Number and Name columns). Thanks.
 

sowjanya

New Member
Apply the filter once again on the column B for the specific string you want to search.
This will show only those rows for which that string is present in Column B.
If the FilteredCount variable is greater than 0 then do a 'Get Worksheet as Collection' which will return a collection of the Filtered rows. You can now dump this collection to the new workbook.
If you do not want to check the FilteredCount variable then first do the 'Get Worksheet as Collection' and then count the rows in the collection using single available action. If it is not zero then dump this collection to the new workbook.

Hi Sir, i tried the above way of filtering excel data. i'm able to filter successfully for 2 criteria. But for 3rd and more its giving error as below. Could you please guide.

sheet.range(varUsedRange).AutoFilter(Field:=1,Criteria1:="CMP-0487533", Operator:=xlOr, Criteria2:="CMP-0396934", Operator:=xlOr, Criteria3:="CMP-0396939")

error:
Exception from HRESULT: 0x800A03EC
 
Top