MS Excel - Find for UiPath Studio

Home/UiPath/MS Excel - Find for UiPath Studio

17 of 28

Hover over an image to enlarge

Write a review

$0.00

Vendor: Q4 Associates

This product is electronically distributed.

Add to wish list Compare

Description
Features
Reviews
Product inquiry

Description	Provides useful functions for finding cells within MS Excel workbooks based on particular properties. Please note these activities are not mandatory to be used within Excel application scope. The Excel sheet gets attached to the UiPath with the workbook name provided as input.
Prerequisites	NA
Execution	Kindly import the NuGet package to use the activities
Inputs	FindCellReferencesByValue WorksheetName (string) - Optional. Default to be the active Worksheet WorkbookName (string) - Optional. Default to be the active Worksheet. Range (String) – Optional. Default to be the used range lookedValue (String) – Wildcard "" can be used at beginning or end for fuzzy search FindHighlightByColor* WorksheetName (string) - Optional. Default to be the active Worksheet WorkbookName (string) - Optional. Default to be the active Worksheet. Range (String) – Optional. Default to be the used range color (String) – Required. Highlight Color to search by. RowOrColumn (bool) – Optional. Default to be true. Search for highlighted row or column. True -> Row, False -> Column FindStrikethroughCells WorksheetName (string) - Optional. Default to be the active Worksheet WorkbookName (string) - Optional. Default to be the active Worksheet. Range (String) – Optional. Default to be the used range Strikethrough (bool) – Optional. True (Default): Select all striked cells in the range. False: Select all un-striked cells in the range OpenWorkbookByPassword WorkbookFullPath (string) - Required.Full Path of Workbook Password (string) - Optional. If no password, just leave it empty. MaxTimeForWaiting (int) - Optional. Default to be 120s. CloseAllInstances Nil
Outputs	FindCellReferencesByValue colRef (DataTable) – The return collection has 3 columns: Ref (text), Column (text), Row (Number). Success (bool) – True if succeeded ErrorMes (string) – Error message if any FindHighlightByColor Success (bool) – True if succeeded ErrorMes (string) – Error message if any HighlightCellsCol (DataTable) – Find all the rows or columns in that range that match the color. The collection contain 3 columns: "Row,Column,Address". For example: 3,5,"E3" FindStrikethroughCells Success (bool) – True if succeeded ErrorMes (string) – Error message if any colRef (DataTable) – The return collection has 3 columns: Ref (text), Column (text), Row (Number). OpenWorkbookByPassword WorkbookFullPath (string) - Required. Full Path of Workbook Password (string) - Optional. If no password, just leave it empty. MaxTimeForWaiting (int) - Optional. Default to be 120s. CloseAllInstances Nil
Components Included	Process Main NuGet Package MSExcelFilter
List of Functionality	Filter CreateRangeRef will be used here. Example: For a table A4:E10, the row of the header is the small number between the row number of the Start Cell and End Cell. If the row number is <4, Row4 will be taken as the header; If the row number is between 4 and 10, the header will be that row; If the row number is >10, throw an error: "This can't be applied to the selected range". The larger number between the Start and End Cell has te be in the Start-End Region, or It'll pop us an error: "This can't be applied to the selected range". The filter will be applied just below the header to the last row of the table, no matter what the last row in the Start-End Cell Region is. The columns of the header will be the overlapped columns betweent he Start-End Cell Region and the table. If no overlap, throw an error: "This can't be applied to the selected range". The only exception of above is that if the Start Cell = End Cell and it's in the table or in the one-cell border of the table: like F3, F10, the header will be the first line of the table; and the filter applied region is the whole table. Another thing need to be consided is that "Field" should not be our of the range of the Start-End Cell Region. 1 means the first column of the Start-End Cell Region. Like the range is D6:E9, the Field could only be 1 or 2. If the Field is 3, an error will appear: "AutoFilter method of Range class failed." Notice: filter date, use "yyyy-MM-dd" format. Because the Excel uses US format DateTime Example: ("A3:H8","=",,,2,,) -> means filter range "A3:H8" to show all the rows with Column B is blank ................. Example: ("A3:H8","<>",,,2,,) -> means filter range "A3:H8" to show all the rows with Column B is not blank ............... Example: ("D3:H28",5,3,,,,) -> means filter range "D3:H28" to show all the rows with Column D ranks the top 5 (don't consider the blank cell) ............... Example: ("D3:H28",5,4,,,,) -> means filter range "D3:H28" to show all the rows with Column D ranks the bottom 5 (don't consider the blank cell) ............... Example: ("D3:H28",">-10",1,"<=14",1,"Sheet1","Score.xlsx") -> means filter range "D3:H28" to show all the rows with Column D's value larger than -10 and less or equal than 14. No empty or string will be considered ............... Operator (all the eligible rows will be selected out, but the result will not be sorted in order): Wildcard * works for "=" xlAnd (1) Logical AND of Criteria1 and Criteria2................. xlOr (2) Logical OR of Criteria1 or Criteria2.................. xlTop10Items (3) Highest-valued items displayed (number of items specified in Criteria1 if Criteria1 is empty, filter will not work)................... xlBottom10Items (4) Lowest-valued items displayed (number of items specified in Criteria1 if Criteria1 is empty, filter will not work).................. xlTop10Percent (5) Highest-valued items displayed (percentage specified in Criteria1, if Criteria1 is empty, filter will not work).................. xlBottom10Percent (6) Lowest-valued items displayed (percentage specified in Criteria1 if Criteria1 is empty, filter will not work).................. xlFilterValues (7) Filter values................. xlFilterCellColor (8) Color of the cell................. xlFilterFontColor (9) Color of the font................. xlFilterIcon (10) Filter icon ................. xlFilterDynamic (11) Dynamic filter. It has several sub filters criteria1 Notice! These enum are written in Criteria1 ----> xlFilterToday = 1 ----> xlFilterYesterday = 2 ----> xlFilterTomorrow = 3 ----> xlFilterThisWeek = 4 ----> xlFilterLastWeek = 5 ----> xlFilterNextWeek = 6 ----> xlFilterThisMonth = 7 ----> xlFilterLastMonth = 8 ----> xlFilterNextMonth = 9 ----> xlFilterThisQuarter = 10 ----> xlFilterLastQuarter = 11 ----> xlFilterNextQuarter = 12 ----> xlFilterThisYear = 13 ----> xlFilterLastYear = 14 ----> xlFilterNextYear = 15 ----> xlFilterYearToDate = 16 ----> xlFilterAllDatesInPeriodQuarter1 = 17 ----> xlFilterAllDatesInPeriodQuarter2 = 18 ----> xlFilterAllDatesInPeriodQuarter3 = 19 ----> xlFilterAllDatesInPeriodQuarter4 = 20 ----> xlFilterAllDatesInPeriodJanuary = 21 ----> xlFilterAllDatesInPeriodFebruray = 22 ----> xlFilterAllDatesInPeriodMarch = 23 ----> xlFilterAllDatesInPeriodApril = 24 ----> xlFilterAllDatesInPeriodMay = 25 ----> xlFilterAllDatesInPeriodJune = 26 ----> xlFilterAllDatesInPeriodJuly = 27 ----> xlFilterAllDatesInPeriodAugust = 28 ----> xlFilterAllDatesInPeriodSeptember = 29 ----> xlFilterAllDatesInPeriodOctober = 30 ----> xlFilterAllDatesInPeriodNovember = 31 ----> xlFilterAllDatesInPeriodDecember = 32 ----> xlFilterAboveAverage = 33 ----> xlFilterBelowAverage = 34 xlFilterNoFill (12): Criteria1->"-4142" : filter by "No Fill" Filter between two date: date format should be "mm/dd/yyyy": >=DateA xlAnd <=DateB Use Array Filter for more than 2 criterias. Use it like: Criteria1="{11}{22}{}", Operator and Criteria2 coule be empty. It will filter by 11, 22 and blank cell. UnFilter Unfilter the whole worksheet OpenWorkbookByPassword - The handler is from the default instance which is the first instance. This function could open the Excel file directly from Remove Server by using the http or https path or UNC path, while the standard Excel VBO could only use UNC path. CloseAllInstances - Closes all instances of excel which have been opened by this business object, either through "Create Instance" or "Open Instance" and which have not been discarded through "Close Instance" or "Release Instance"

Disclaimer: References in this site to UiPath products, activities or services or the use of any UiPath trade name are made solely to indicate compatibility of products available on RPA Marketplace with UiPath products, and do not constitute or imply the development, endorsement, recommendation or approval by UiPath of the same.