MS Excel - Misc Data Operations for UiPath Studio

Description

Provides various data operations for working with MS Excel workbooks, including Text to Columns, Creating a range reference and more. 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 

CreateRangeRef

WorksheetName (string) - Optional. Default to be the active Worksheet

WorkbookName (string) - Optional. Default to be the active Worksheet.

StartCellRef (String) – Optional. Default to be "A1"

EndCellRef (String) – Optional. Default to be the last cell of the Used Range.

GetInformationFromRangeRef

RangeRef (String) – Range reference from where information needs to be fetched

GetPixelPositionofaRange

WorksheetName (string) - Optional. Default to be the active Worksheet

WorkbookName (string) - Optional. Default to be the active Worksheet.

RangeRef (String) – Required. A range in Excel, like: "C1:F8"

GetTableReference

WorksheetName (string) - Optional. Default to be the active Worksheet

WorkbookName (string) - Optional. Default to be the active Worksheet.

AnchorCellRef (String) – Optional. If the table is very large (more than 30000 rows), this field is required. It refers to the header of a column that has no blank cells in the table. Recommand always use it.

ColumnsLetters (String) – Optional. Default to be all the columns in the table's "TableVisibleRowsRef". It takes the intersection of this range and the table. Separated by comma ",".  For Example: "L:M,P:P,S:S". This argument only influences the value of "TableVisibleRowsRef"

RefreshAll

WorkbookName (string) - Optional. Default to be the active Worksheet.

SetValueToUnhiddenRange

WorkbookName (string) - Optional. Default to be the active Workbook.

WorksheetName (string) - Optional. Default to be the active Worksheet

Range (string) - Required..

Value (String) – Optional. Default to clear all the values in that range.

SortTable

WorkbookName (string) - Optional. Default to be the active Worksheet.

WorksheetName (string) - Optional. Default to be the active Worksheet

StartCell (string) - Optional. Default to be the first cell of the Used Range.

EndCell (String) – Optional. Default to be the last cell of Used Range

field1 (string) -    Optional. Sorting on which field firstly, like: "A:A" Or any range within that column like "B4". If you put a range contains several columns like: "C3:H5", only C3 will be used to determine the filtered column. Default to be the first Column of the range.

Order1 (int) – Optional. XlSortOrder: 1 -> xlAscending, 2  -> xlDescending.  Default to be 1

field2 (string) - Optional. Second sorting field.

Order2 (int) – Optional.

Field3 (string) - Optional. Third sorting field.

Order3 (int) – Optional.

Header (int) – Optional. Has header or not. 0 -> auto determines; 1 -> has header; 2 -> has no header.    Default to be 2 -  has no header. If the range is within a table, it will always be auto determines, no matter what value you put here.

Casesensitive (boolean) – Optional. true -> case sensitive, false -> case insensitive.    Default to be false

Orientation (int) – Optional. 1 -> Sort by columns, 2 -> Sort by rows.   Default to be 1

TextToColumns

WorkbookName (string) - Optional. Default to be the active Worksheet.

WorksheetName (string) - Optional. Default to be the active Worksheet

CellRef (string) - Optional. Default to be "A1".

ConsecutiveDelimiter (bool) - Optional. Default to be False. True to have Microsoft Excel consider consecutive delimiters as one delimiter.

Delimiter (string) - Optional. Default to be ",". Other selection: "Tab", " ", ";", . You can also use combination of these char, like: “, space" is a combination of comma and space, so both comma and space will be taken as the delimiter

RangeRef (string) - Optional. Where to apply the TextToColumns. Default to be the UsedRange.

OpenWorkbookByPassword

WorkbookFullPath (string) - Required.Full Path of Workbook

Password (string) - Optional. If no password, just leave it empty.

Delimiter (string) - Optional. Default to be 120s.

CloseAllInstances

Nil

Outputs

CreateRangeRef

CreatedRangeRef (String) – Range reference which has been created.

GetInformationFromRangeRef

StartCell (String) – Value of StartCell.

EndCell (String) – Value of EndCell.

StartColumn (String) – Value of StartColumn.

EndColumn (String) – Value of EndColumn.

StartRow (int) – Value of StartRow.

EndRow (int) – Value of EndRow.

GetPixelPositionofaRange

Left (int) – Left Pixel value.

Top (int) – Top Pixel value.

Right (int) – Right Pixel value.

Bottom (int) – Bottom Pixel value.

GetTableReference

TableHeaderRef (String) – Table header reference

TableContentRef (String) – Table Contentreference

TableNonEmptyBodyRef (String) – Table Non-Empty body reference

TableVisibleRowsRef (String) – Table visible rows’ reference

RefreshAll

Nil

SetValueToUnhiddenRange

Success (bool) – True if succeeded

ErrorMes (string) – Error message if any

SortTable

Success (bool) – True if succeeded

ErrorMes (string) – Error message if any

TextToColumns

Nil

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

MSExcelPictureOperations

List of Functionality

CreateRangeRef

Example:

StartCell:A4  EndCell: C9   The output is  A4:C9

StartCell:A4  EndCell: C     The output is A4:C156  (156 is the last used row in the worksheet)

StartCell:A4  EndCell: 20   The output is A4:AD20 (AD is the last used column in the worksheet)

StartCell A4  EndCell:        The output is A4: AD156

Start-> F:F  End-> G   is different from  Start->F  End->G. The previous is Column F intersect UsedRange; Latter one is F:G intersect Used Range

Notice:  If one of StartCell or EndCell is empty, another one cannot be pure RowNumber or Pure Column Letter. Example: StartCell is empty, EndCell is K -> incorrect

If StartCellRef contains ":", and EndCellRef contains ":". It will return the intersection range.

If One of StartCellRef and EndCellRef contains ":", another doesn't contain ":", it will return the intersection between the StartCellRef / EndCellRef( if it has ":") and UsedRange;

GetInformationFromRangeRef

Input a reference of a range, output the first cell reference, last cell reference, first column, last column, first row, last row.

GetPixelPositionofaRange

Get the Position of a range in a Worksheet. Return the Top, Buttom, Left, Right ( in pixels )

GetTableReference

Return the reference of table

. If there is not table it will return the reference of the Used Range but skip the hidden rows, and put the value into "TableVisibleRowsRef"

RefreshAll

Equal to "RefreshAll" Button

SetValueToUnhiddenRange

Clear or set a value to all the unhidden cells in a range

SortTable

Sort a range based on one or several fields by using rules. The first column of the sorting field must be in the range, otherwise an exception will be thrown.

If it's a table, the filter range will be the whole table content range, doesn't matter the input of "StartCell","EndCell" and "header".

The field1,field2 and field3 could be like: "A:A", "A2:B3", "C3", "C". No matter what it is, the first column of this range will be picked up.

TextToColumns

Delimiter can be: "comma" Or ","

"space" Or " "

"semicolon" Or ";"

"Tab"

For other chars only  the first char will be be considered .

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"