Filter Excel Data By Specific Criteria

dheeraj_kumar

New Member
Hi VJ ,
I am trying to do RESOURCE Pool scheduling in Blue Prism . I have created Pool of 10 Resources and scheduled a Task . Now I am running that Task on Pool . It is running randomly on 1 Resource out of 10 and got completed and not running on rest of the Resources. Please suggest how to run task on all Resources of a Pool .
 

BernieBenitez

New Member
Hello, I am trying do the same activity but I have a question:
How may I use several filters in this code line: "
sheet.range(varUsedRange).AutoFilter (Field:=1, Criteria1:=1, Operator:=xlOr, Criteria2:=2)
"

Thanks
 

VJR

Well-Known Member
Hello, I am trying do the same activity but I have a question:
How may I use several filters in this code line: "
sheet.range(varUsedRange).AutoFilter (Field:=1, Criteria1:=1, Operator:=xlOr, Criteria2:=2)
"

Thanks
Hi BernieBenitez,

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

The above example shown already has multiple conditions where you need to include the numeric constant for xlOr from the above MSDN link provided. As per the above code, Field1 will match Criteria1 OR Criteria2.

If you have two Fields to filter on then you can again add it as a separate line of code similar to the above.
 
i am getting error while checking code- Getworkbook is not declared .it may be inaccessible due to its protection level . Getworksheet is not declared .it may be inaccessible due to its protection level. Please suggest


Hi,

I'm getting the same error as mentioned above for GetWorkbook and GetWorksheet.

I've pasted the code n the code stage by passing the values for the input and output parameters. But, cannot check/run the code because of the compile errors for GetWorkbook and GetWorksheet. Someone please help me to solve this.

Thanks
 

viraj

Member

VJR

Well-Known Member
Hi,

I'm getting the same error as mentioned above for GetWorkbook and GetWorksheet.

I've pasted the code n the code stage by passing the values for the input and output parameters. But, cannot check/run the code because of the compile errors for GetWorkbook and GetWorksheet. Someone please help me to solve this.

Thanks
Hi AAH,

The problem you have posted is from Post #11 and the solution to it is in Post #13 and #14.
 

Shashanth S K

New Member
Hello VJR I want to Filter Column I with Particular value(text),
i have copied the Above code, and while filtering Below is the code i have modified
sheet.range(varUsedRange).AutoFilter (Field:=9, Criteria1:="IPD")

i do not have the Column heading,
 

VJR

Well-Known Member
Hello VJR I want to Filter Column I with Particular value(text),
i have copied the Above code, and while filtering Below is the code i have modified
sheet.range(varUsedRange).AutoFilter (Field:=9, Criteria1:="IPD")

i do not have the Column heading,
Hi Shashanth S K,

The changes you made appear to be correct.
Are you facing any issues while doing so?
It is better to have column headings, but the filter will work and while dealing with the filtered data you will also need to consider the first row that has the data in place of the headings.
 

Serik

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,

I am trying to implement multiple filters with multiple criterias.
For multiple critierias I want to use Operator:=xlAnd
However, I'm getting compiler error that xlAnd is not declared. Where should I declare this operator. I'm writing code inside of Excel VBO
 

VJR

Well-Known Member
Hi,

I am trying to implement multiple filters with multiple criterias.
For multiple critierias I want to use Operator:=xlAnd
However, I'm getting compiler error that xlAnd is not declared. Where should I declare this operator. I'm writing code inside of Excel VBO
Hi Serik,

You may have missed this below comment in the code.

'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
 

Serik

New Member
Hi Serik,

You may have missed this below comment in the code.

'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

Ok, I understood. Thank you

But, when I want to use more than 2 criterias, I'm getting an error. My code looks following:
sheet.range(varUsedRange).AutoFilter (Field:=14, Criteria1:=0, Criteria2:=11, Criteria3:=25, Operator:=2)

Also, I tried to put Operator between criterias and I'm still getting an error.
For 2 criterias, It works properly, but for more than 2, it's not. Actually, I just want to filter column, where cells are equal to 0, 11 and 25. Could you suggest a solution?
 

VJR

Well-Known Member
Hi Serik,

It was actually for reasons like these I have mentioned a note as follows.
This is just a sample working code under general conditions. You will need to amend it as per your requirement.

The Excel AutoFilter method takes only two criteria. In order to add more you will need to pass an Array with as many values as needed. Information on arrays can be found here or you can even try searching for a direct code that passes an array to the AutoFilter method.
 

Kiran123

New Member
Hi VJR,

first of all thanks a lot for your effort,

suppose if i want to filter all the rows containing the letter 'o', in the second column in your example how can i do that?
 

VJR

Well-Known Member
Are you referring to this?
1523886200273-png.572

Try with this and let me know your observations.
sheet.range(varUsedRange).AutoFilter (Field:=2, Criteria1:="*o*")
 

Serik

New Member
Hi Serik,

You may have missed this below comment in the code.

'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
Is it possible to filter to values, which is not in my list. So that I have an array of values, but I want to filter to those values, which are not in this array - to EXCLUDE these values. I tried to do in this way array("<>1", "<>2", ... "<>n"), but it throws an error. Is there some other alternatives?
 

VJR

Well-Known Member
Is it possible to filter to values, which is not in my list. So that I have an array of values, but I want to filter to those values, which are not in this array - to EXCLUDE these values. I tried to do in this way array("<>1", "<>2", ... "<>n"), but it throws an error. Is there some other alternatives?
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.
 
Top