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" |
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.
No posts found