MS Excel - Formulas for UiPath Studio

Description

Provides utility functions for working with formulas in MS Excel workbooks. 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 

FormulaGetResult

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

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

FormulaString (String) – Required.

SetCellValueByFormula

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

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

FormulaString (String) – Applied formula

Cellref (String) – Required. The address of a cell.

GetCellFormula

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

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

Cellref (bool) – Required. Address of the cell

SetRangeValueSelfReferenced

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

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

FormulaString (String) – Required. Applied formula. Use the "$$$" to represent the value of cells: Example:   "=$$$*-1"

RangeRef (string) – Required. The address of a 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

FormulaGetResult

VLookupResult (string) – Formula Result

Success (bool) – True if succeeded

ErrorMes (string) – Error message if any

SetCellValueByFormula

FormulaResult (string) – FormulaResult

Success (bool) – True if succeeded

ErrorMes (string) – Error message if any

GetCellFormula

CellFormula (string) – Cell Formula

Success (bool) – True if succeeded

ErrorMes (string) – Error message if any

SetRangeValueSelfReferenced

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

MSExcelFormulas

List of Functionality

FormulaGetResult

Run formula to return a result form the worksheet. It will not change any value in the worksheet.

Example: ("=A12*C5","Sheet1","Student Scores.xlsx"). Example: ("=SUM(P6:P13)",,"Student Scores.xlsx"). Example: ("=RIGHT(P9,2)",,,)

SetCellValueByFormula

This action is used to apply a formula in a cell, and the result will be returned.

The difference from "Formula Get Result" action is that, "Formula Get Result" will not make any change to the worksheet, while this action will reset the value of the referenced cell.

The formula can refer to external Workbook.

Example: ("=SUM('C:\Users\DEV.User1\Desktop\Robot\[Example Application.xlsx]PDF Master Sheet'!E2:G3)","A12","Sheet1","Student Scores.xlsx")..........

Example: ("=RIGHT(P9,2)","A41",,,)..........

Example: ("=HYPERLINK(""C:\Pictures\Image1.png"")", "B1",,,)

GetCellFormula

Retrieve the text of formula applied in a cell. If no formula is there, an empty string will be returned. Example: ("A12","Sheet1","Student Score.xlsx")

SetRangeValueSelfReferenced

Use the "$$$" to represent the value of cells:

For example:

( "A2:H60", "=$$$*5-$$$/3" )

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"