Filter Excel Data By Specific Criteria

qqq

New Member
#41
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"
 
#42
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
Staff member
#43
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
 

VJR

Well-Known Member
Staff member
#44
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.
 
#45
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

#46
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
Staff member
#47
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.
 
#48
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
 
#49
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)
 
#50
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.
 
#51
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!
 
#54
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

Last edited:
#55
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.
 
#56
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.
 
Top