dheeraj_kumar
New Member
Thanks
Hi BernieBenitez,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
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
Hi
Please check on below link
http://www.rpaforum.net/threads/filter-excel-data-by-specific-criteria.992/post-4208
Hi AAH,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 Shashanth S K,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,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 Serik,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
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?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
You're welcome!first of all thanks a lot for your effort
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.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?