Set Value in Excel

Defines a value to a cell in Excel.

Command availability: IBM RPA SaaS and IBM RPA on premises

Description

Defines a value to a cell in Excel through row and column coordinates that are assigned by the user.

Script syntax

IBM RPA's proprietary script language has a syntax similar to other programming languages. The script syntax defines the command's syntax in the script file. You can work with this syntax in IBM RPA Studio's Script mode.

excelSet [--value(String)] [--isformula(Boolean)] --file(Excel) [--getfirstsheet(Boolean)] --sheet(String) [--usingcell(Boolean)] --cell(String) --row(Numeric) --column(Numeric)

Dependencies

  • To use this command, open an Excel file with the Open Excel file (excelopen) command.

Input parameters

The following table displays the list of input parameters available in this command. In the table, you can see the parameter name when working in IBM RPA Studio's Script mode and its Designer mode equivalent label.

Designer mode label Script mode name Required Accepted variable types Description
Value value Optional Text Value to be entered in the cell.
Formula isformula Optional Boolean Used if the value assigned in the Value parameter is a formula.
See the Supported functions
File file Required Excel Variable that stores the Excel instance.
Use first sheet getfirstsheet Optional Boolean Causes the first spreadsheet of the file to be used.
Sheet sheet Required when the Use first sheet parameter is disabled Text Name of the spreadsheet to be used.

If you enter a name that does not match any spreadsheet in the file, a new one is created with the given name.
Specify cell usingcell Optional Boolean Allows the user to enter the complete cell coordinate.

Example: "A1".
Cell cell Required when the Specify cell parameter is enabled Text Coordinate of the cell used to assign the value to.

Example: "A1".
Row row Required when the Specify cell parameter is disabled Number Corresponds to the row in which the cell is located.

Example: "1" in "A1".
Column column Required when the Specify cell parameter is disabled Number Corresponds to the column in which the cell is located.

Example: "A" in "A1".

Supported Functions

The following lists show each function that is supported by the Set Value in Excel (excelset) command.

Database

  • DAVERAGE
  • DCOUNT
  • DCOUNTA
  • DGET
  • DMAX
  • DMIN
  • DSUM
  • DSVAR
  • DVARP

Date and time

  • DATE
  • DATEVALUE
  • DAY
  • DAYS
  • DAYS360
  • EDATE
  • EOMONTH
  • HOUR
  • ISOWEEKNUM
  • MINUTE
  • MONTH
  • NETWORKDAYS
  • NETWORKDAYS.INTIL
  • NOW
  • SECOND
  • TIME
  • TIMEVALUE
  • TODAY
  • WEEKDAY
  • WEEKNUM
  • WORKDAY
  • YEAR
  • YEARFRAC

Information

  • ERROR.TYPE
  • ISBLANK
  • ISERR
  • ISERROR
  • ISEVEN
  • ISLOGICAL
  • ISNA
  • ISNONTEXT
  • ISNUMBER
  • ISODD
  • ISTEXT
  • N
  • NA
  • TYPE

Logical

  • AND
  • FALSE
  • IF
  • IFERROR
  • IFNA
  • NOT
  • OR
  • TRUE

Lookup and reference

  • ADDRESS
  • CHOOSE
  • COLUMN
  • COLUMNS
  • HLOOKUP
  • INDEX
  • INDIRECT
  • LOOKUP
  • MATCH
  • OFFSET
  • ROW
  • ROWS
  • VLOOKUP

Math and trig

  • ABS
  • ACOS
  • ACOSH
  • ASINH
  • ATAN
  • ATAN2
  • ATANH
  • CEILING
  • COS
  • DEGREES
  • EXP
  • FACT
  • FLOOR
  • INT
  • LN
  • LOG
  • LOG10
  • MOD
  • PI
  • POWER
  • PRODUCT
  • QUOTIENT
  • RAND
  • RANDBETWEEN
  • ROUND
  • ROUNDDOWN
  • ROUNDUP
  • SIGN
  • SIN
  • SINH
  • SQRT
  • SQRTPI
  • SUMSQ
  • TAN
  • TANH
  • TRUNC

Statistical

  • AVERAGE
  • AVERAGEA
  • AVERAGEIF
  • AVERAGEIFS
  • COUNT
  • COUNTA
  • COUNTBLANK
  • COUNTIF
  • COUNTIFS
  • LARGE
  • MAX
  • MAXA
  • MEDIAN
  • MIN
  • MINA
  • RANK
  • RANK.AVG
  • RANK.EQ
  • SMALL
  • STDEV
  • STDEV.P
  • STDEV.S
  • STDEVP
  • VAR
  • VARP

Text

  • CHAR
  • CONCATENATE
  • EXACT
  • FIND
  • FIXED
  • HYPERLINK
  • LEFT
  • LEN
  • LOWER
  • MID
  • PROPER
  • REPLACE
  • REPT
  • RIGHT
  • SEARCH
  • SUBSTITUTE
  • T
  • TEXT
  • TRIM
  • UPPER
  • VALUE

Example

Opens an Excel file and assigns the file to the variable "OpenExcel File". The Set Value in Excel command inserts the value "Example" in the cell corresponding to row 5 and column 5 the "sheet1" of the file.

defVar --name excelFile --type Excel
excelOpen --file "excelFile.xlsx" --savechanges  excelFile=value
excelSet --value Example --file ${excelFile} --sheet sheet1 --row 5 --column 5
excelClose --file ${excelFile} --save

Limitations

  • If there is a value in the defined coordinate, this value is replaced by the entered value.
  • Not every excel function is supported. See the Supported functions