Filter Excel Data By Specific Criteria

VJR

Well-Known Member
Hi viraj,

Can you provide more details of what have you got so far in your process?
On clicking the View attachment link it shows as "The requested page could not be found."
 

viraj

Member
hi vjr,

Its just a screenshot of how I wanted to filter data actually I haven't found way to directly filter the data from excel but I managed to do it by Increasing few more steps and using excel function I will really appreciate if you can provide a mechanism to Do filtering Directly data using Excel filters and For the Missing Screenshot please find below,

Really Appreciate your help.

1523874137114.png
 

VJR

Well-Known Member
Hi viraj,

Can you tell what does "...a mechanism to Do filtering Directly data using Excel filters..." indicate?
Are you saying by opening the file, spying the Filter option of Excel?
or apply filters via a new object action in the Ms Excel VBO?
 

viraj

Member
Hi Vjr,

I mean to use normal filters as shown in the above screenshot I tried with the spying the filter but there I have to deal with problem in selecting the filter dropdown and the option I have tried surface automation also but the object doesn't seems to stable and most of the times object identified via surface automation doesn't came out right I didn't try applying filters through New VBO.
 

VJR

Well-Known 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:
1523886200273.png


Output after running the object:
1523886266236.png
 

Attachments

  • 1. Object diagram.JPG
    1. Object diagram.JPG
    24.6 KB · Views: 826
Last edited:

VJR

Well-Known Member
Hi viraj,

You will need to call this object action from the process.
The process should first do a create instance, then Open action before calling the Filter action. The start stage of the action should contain the appropriate parameters that are used in the code. Let me know how it goes.
 

dheeraj_kumar

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

VJR

Well-Known Member
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
It appears that you are writing this code outside of the MS Excel VBO.
 

dheeraj_kumar

New Member
Thanks for your reply , I was writing code outside the Ms Excel VBO . Now I have written inside MS Excel VBO and I am not getting error . I have also provided inputs and run the code but it is not applying filter. I have provided these inputs anything else required to do in the code or as input.
 

VJR

Well-Known Member
Hi dheeraj_kumar,

Yes, you need to modify the code as per your requirement.
For eg in the below line of code.

sheet.range(varUsedRange).AutoFilter (Field:=1, Criteria1:=1)

This line of code is for Field 1 (which means Column A) and Criteria1 = 1 meaning filter all the 1's in Column A as in the below example.

Input before running the object:
1528278139232.png

Output after running the object:
1528278158771.png

So you need to customise the code as per your needs.
Also you can modify the Object to pass the column alphabet and the criteria as parameters to the Object and the code should handle it accordingly.
 
Last edited:

dheeraj_kumar

New Member
Thanks for your response VJ, It is working fine now. I have to search records in excel after applying filter on specific criteria and search the keywords list in excel and paste output in other excel . I have applied filter and the next step is to search keyword in excel . Do you have any code to search keyword list and paste result in other excel .
 

VJR

Well-Known Member
Thanks for your response VJ, It is working fine now. I have to search records in excel after applying filter on specific criteria and search the keywords list in excel and paste output in other excel . I have applied filter and the next step is to search keyword in excel . Do you have any code to search keyword list and paste result in other excel .
What do you mean by keywords list?
 

dheeraj_kumar

New Member
Using VB.NET i want find a specific string that occurs in column B of an Excel sheet and copy that entire row and paste it in a new worksheet. This string occurs a couple of times in this B column.
 

VJR

Well-Known 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.
 
Top