MS Excel - Editing for UiPath Studio

Description

Provides extended editing functions for MS Excel workbooks in conjunction with the standard UiPath Excel activities. 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 

InsertColumns

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

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

Range (String) - Required

InsertRows

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

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

Range (string) - Required

AutofillRange

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

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

XlAutoFillType (string) - Optional. Default is 0 which means Excel will determine how to fill the range.

Range (String) - Required. The range to fill. The first cell of this range will be used as the base to apply the fill Type

ClearCells

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

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

Range (String) - Optional. Default to be all used cells.

ClearSelection (int) – Optional.Default to be 0, which means to clear All

ClearSheetOrDeleteRC

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

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

RowOrColumn (bool) – Optional. true means row; false means column; default to be row

FromIndex (int) - Optional. Default to be 1

ToIndex (int) – Optional. Default to be the end row or column of the used range

OnlyVisible (bool) – Optional. Default only visible rows or columns. Default to be false

DeleteDiscreteRowsOrColumns

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

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

RowOrColumn (bool) – Optional. Default to be true which means Row.

RowsColumns (string) – Required. Like: "1:1,2:3,5:6,8:8" Or "F:F,M:N,G:G,T:Z" Or "A2:C3,A5:Z5,A9"

GetCellValRefAfterMovingNext

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

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

MoveDirection (int) – Optional. 0 Means move down; 1 means up; 2 means left; others means right. Default to be 0 - Down.

CellRef (string) – Required. Move from which cell

MoveNextCellCTRL

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

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

MoveDirection (int) – Optional. Down, Up, Right, Left. Default to be Down. Case-Insensitive

CellRef (string) – Required. Move from which cell

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

InsertColumns

Success (bool) – True if succeeded

ErrorMes (string) – Error message if any

InsertRows

Success (bool) – True if succeeded

ErrorMes (string) – Error message if any

AutofillRange

Success (bool) – True if succeeded

ErrorMes (string) – Error message if any

ClearCells

Success (bool) – True if succeeded

ErrorMes (string) – Error message if any

ClearSheetOrDeleteRC

Success (bool) – True if succeeded

ErrorMes (string) – Error message if any

DeleteDiscreteRowsOrColumns

Nil

GetCellValRefAfterMovingNext

NextCellRef (string) –

NextCellValue (string) – True if succeeded

NextCellValue2 (string) – True if succeeded

NextCellDisplayedValue (string) – True if succeeded

NextCellFormula (string) – True if succeeded

MoveNextCellCTRL

NextCellRef (string) – True if succeeded

NextCellValue (string) – True if succeeded

NextCellValue2 (string) – True if succeeded

NextCellDisplayedValue (string) – True if succeeded

NextCellFormula (string) – True if succeeded

OpenWorkbookByPassword

Name (string) - The name used by Excel to identify the workbook - usually this is the file name (without the path).

CloseAllInstances

Nil

Components Included

Process

Main

NuGet Package

MSExcelEditing

List of Functionality

InsertColumns

Inserting  columns in worksheet.

InsertRows

Inserting  Rows  in worksheet.

AutofillRange

Fill a range with value or formate. The first row of the range will be taken as source range. If apply formular, could choose 0, 2, 9 or 10 as XlAutoFillType.

XlAutoFillType:

xlFillDefault (0) -> Excel determines the values and formats used to fill the target range................

xlFillCopy (1) -> Copy the values and formats from the source range to the target range, repeating if necessary.................

xlFillSeries (2) -> Extend the values in the source range into the target range as a series (for example, '1, 2' is extended as '3, 4, 5'). Formats are copied from the source range to the target range, repeating if necessary.................

xlFillFormats (3) -> Copy only the formats from the source range to the target range, repeating if necessary.................

xlFillValues (4) -> Copy only the values from the source range to the target range, repeating if necessary.................

xlFillDays (5) -> Extend the names of the days of the week in the source range into the target range. Formats are copied from the source range to the target range, repeating if necessary.................

xlFillWeekdays (6) -> Extend the names of the days of the workweek in the source range into the target range. Formats are copied from the source range to the target range, repeating if necessary.................

xlFillMonths (7) -> Extend the names of the months in the source range into the target range. Formats are copied from the source range to the target range, repeating if necessary.................

xlFillYears (8) -> Extend the years in the source range into the target range. Formats are copied from the source range to the target range, repeating if necessary.................

xlLinearTrend (9) -> Extend the numeric values from the source range into the target range, assuming that the relationships between the numbers is additive (for example, '1, 2,' is extended as '3, 4, 5', assuming that each number is a result of adding some value to the previous number). Formats are copied from the source range to the target range, repeating if necessary.................

xlGrowthTrend (10) -> Extend the numeric values from the source range into the target range, assuming that the relationships between the numbers in the source range are multiplicative (for example, '1, 2,' is extended as '4, 8, 16', assuming that each number is a result of multiplying the previous number by some value). Formats are copied from the source range to the target range, repeating if necessary

ClearCells

Clear Contents or formats or hyperlinks or all above.

ClearSelection: 0 -> All   1 -> contents  2 -> formats  3-> hyperlinks

ClearSheetOrDeleteRC

Delete rows or columns from FromIndex to ToIndex.

If FromIndex and ToIndex are both omitted, clear the worksheet;

If only FromIndex is Omitted, FromIndex is set to 1; If only ToIndex is omitted, ToIndex is set to be end row or column of the used range.

RowOrColumn determines delete rows or delete columns.

Example: (,,,,) -> clear whole worksheet

Example: (False,5,8,,) -> Delete columns from 5th to 8th

Example: (True,,8,,) -> Delete rows from the first to the 8th

Example: (,5,,,) -> Delete rows from the 5th to the last used row.

DeleteDiscreteRowsOrColumns

txtRowsColumns Could be:  Like: "A2:C3,A5:Z5,A9". The corresponding rows or columns will be deleted. No need to worry about the length of txtRowsColumns. It can supass 255 characters.

GetCellValRefAfterMovingNext

Skip Invisiable Cell

0 - Move Down (Default)

1 - Move Up

2 - Move Left

Others - Move Right

MoveNextCellCTRL

Equals to

CTRL+Down,

CTRL+UP,

CTRL+Left,

CTRL+Right

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"