MS Excel

The MS Excel functions allow you to retrieve data from, and write data to, MS Excel files.

The file on which the function is to be run must not be open in Excel when the function is performed.

The functions are divided into two groups, With Excel and Without Excel:

The difference between the two sets of functions is as follows:

With Excel

These functions do require that Excel be installed on the machine on which the solution is being run. Excel is opened every time one of these functions is performed. These functions therefore take longer to perform than the functions under Without Excel.
Without Excel

These functions do not require that Excel be installed on the machine on which the solution is being run. The function is performed without opening Excel and is therefore performed very quickly.

These functions are valid only for Excel files generated using Office 2007 onwards (i.e. that have .XLSX extensions).

The Excel file must not be open in Excel when one of these functions is to be performed.

All Excel functions that read data from Excel files, including functions under With Excel and under Without Excel, can cause problems that prevent the project from running if the Excel file includes hyperlinked content. Hyperlinks should be removed from files that will be accessed by these functions.

MS Excel Functions - With Excel

(Available from version 7.0 onwards.)

These functions do require that Excel be installed on the machine on which the solution is being run. Excel is opened every time one of these functions is performed. These functions therefore take longer to perform than the functions under Without Excel.

Many of the functions in this library are available in two versions, as in the examples below.

From Active Excel

These functions are performed on the Excel file that is currently open and active in Excel. The Excel window itself must be the active window.

These functions do not require that you specify the path or name of the Excel file - they are performed on whichever Excel file is currently open and active.
From Specified Excel

These functions are performed the Excel file specified within the function's full path parameter.

The specified Excel file must not be open in Excel when one of these functions is to be performed.

Example Files

An individual example is provided for each function described below.

Functions

Copy Data Between Excel Files

Copies a range of cells from one Excel file to a specified sheet and location in another Excel file. The target location is specified as the location of the top left cell from where to paste the copied range.

The target Excel file must exist and must contain a sheet with the name specified.

Parameters

Parameter	Input Type	Description
source full path	text	The full path (including file name) of the Excel file that contains the data to copy
source sheet name	text	The name of the sheet in the source Excel file
start column	number	The column number of the cell at the top-left of the range to retrieve. For example, column A corresponds to column number 1.
end column	number	The column number of the cell at the bottom-right of the range to retrieve. For example, column C corresponds to column number 3.
start row	number	The row number of the cell at the top-left of the range to retrieve.
end row	number	The row number of the cell at the bottom-right of the range to retrieve.
target full path	text	The full path (including file name) of the Excel file to copy data to
target sheet name	text	The name of the sheet in the target Excel file
start from row	number	The row number of the cell at the top-left of the range into which to copy the data
start from column	number	The column number of the cell at the top-left of the range into which to copy the data

Returns

Nothing

Example

The sample file includes the following table on the sheet Sales by Salesperson.

The workflow below copies the table data for the first three months only (as highlighted in the image above) onto Sheet 1 of the Excel file at c:/temp/target.xlsx, from cell B4.

When executed, the specified data is copied to the target file.

Get Count of Rows in Column of Specified Excel

Retrieves the number of non-empty rows in a specified column of a specified sheet of an Excel file.

Parameters

Parameter	Input Type	Description
full path	text	The full path (including file name) of the Excel file
sheet name	text	The name of the sheet in the Excel file
column	text	The column in which to count rows, specified by its letter, for example, C

Returns

Returns the number of non-empty rows in the column as a number.

Example

The sample file includes the following table on the sheet Sales by Salesperson.

The workflow below counts the number of non-empty rows in column F when the Excel file is not open. The result is stored in the number variable number.

When executed, the variable number is set to 13. Although the table has 14 rows (including the header), the cell at F10 is empty.

Get Cell Value from Active Excel

Retrieves the value of a specified cell from a specified sheet of the active Excel file.

Parameters

Parameter	Input Type	Description
sheet name	text	The name of the sheet in the Excel file
column letter	number	The column letter of the cell from which to get the value
row number	number	The row number of the cell from which to get the value

Returns

Returns the cell value as text.

Example

The sample file includes the following table on the sheet Sales by Salesperson.

The workflow below retrieves the value at cell D4 and stores it in the text variable text_var.

When executed, text_var is set to 90 as expected.

Get Cell Value from Specified Excel

Retrieves the value of a specified cell from a specified sheet of the specified Excel file.

Parameters

Parameter	Input Type	Description
full path	text	The full path (including file name) of the Excel file
sheet name	text	The name of the sheet in the Excel file
column letter	number	The column letter of the cell from which to get the value
row number	number	The row number of the cell from which to get the value

Returns

Returns the cell value as text.

Example

The sample file includes the following table on the sheet Sales by Salesperson.

The workflow below retrieves the value at cell D4 and stores it in the text variable text_var.

When executed, text_var is set to 90 as expected.

Get Cell Range Values from Active Excel

Retrieves the contents of a specified range of cells in a specified sheet in the active Excel file.

Parameters

Parameter	Input Type	Description
sheet name	text	The name of the sheet in the Excel file
start row	number	The row number of the cell at the top-left of the range to retrieve
start column	number	The column number of the cell at the top-left of the range to retrieve. For example, column A corresponds to column number 1.
row count	number	The total number of rows to be retrieved.
column count	number	The total number of columns to be retrieved.

Returns

Returns a list of rows. Each element of each row holds the value of a single cell from the selected range.

Example

The sample file includes the following table on the sheet Sales by Salesperson.

The workflow below retrieves the sales numbers as highlighted in the image above when the sample file is open in Excel. The data is stored in the variable List_of_Rows (a list variable of type Row).

When executed, List_of_Rows is populated with the requested data spanning 13 rows for 6 elements each.

Get Cell Range Values from Specified Excel

Retrieves the contents of a specified range of cells in a specified sheet of a specified Excel file.

Parameters

Parameter	Input Type	Description
full path	text	The full path (including file name) of the Excel file
sheet name	text	The name of the sheet in the Excel file
start row	number	The row number of the cell at the top-left of the range to retrieve
start column	number	The column number of the cell at the top-left of the range to retrieve. For example, column A corresponds to column number 1.
row count	number	The total number of rows to be retrieved.
column count	number	The total number of columns to be retrieved.

Returns

Returns a list of rows. Each element of each row holds the value of a single cell from the selected range.

Example

The sample file includes the following table on the sheet Sales by Salesperson.

The workflow below retrieves the sales numbers as highlighted in the image above when the sample file is not currently open in Excel. The data is stored in the variable List_of_Rows (a list variable of type Row).

When executed, List_of_Rows is populated with the requested data spanning 13 rows for 6 elements each.

Get Sheet Names from Specified Excel

Retrieves a list of the names of all sheets in a specified Excel file.

Parameters

Parameter	Input Type	Description
full path	text	The full path (including file name) of the Excel file

Returns

Returns the sheet names in a text list.

Example

The sample file includes the following sheets:

The workflow below retrieves the sheet names and stores them in the variable List_of_Text, which is a list of type text.

When executed, the variable List_of_Text is populated with the sheet names.

Inject and Run Macro into Specified Excel

Inserts a VBA script into the specified Excel file, runs the specified macro, and deletes the script from the file. The Excel worksheet must be macro-enabled for this function to work, as below:

The Excel file does not have to be saved with an .xlsm (macro-enabled) extension - the standard format of .xlsx is supported.

Parameters

Parameter	Input Type	Description
full path	text	The full path (including file name) of the Excel file
VBA Script	text	The VBA script
macro name	text	The macro name, as specified within the VBA script itself
visible	Boolean	Whether or not to make Excel visible while the function is performed. If set to False, Excel is not made visible

Returns

Nothing.

Example

The sample file BMI_Report.xlsm includes a sheet called Notes with an explanation of the two macros saved to the file (used in the example for Run Macro in Specified Excel).

This example injects and runs a simple macro to set the cells A1 and A5:C5 to bold.

The macro text is shown below.

The workflow below injects and runs the macro above. Note that the macro name is set to MakeBold to match the name of the macro name within the VBA script. Visible is set to False, so Excel is not opened visibly when running.

When executed, nothing visible happens. However, when opening the Excel file after the workflow has ended, the specified cells are now bolded.

Run Macro in Specified Excel

Runs a specified macro in a specified Excel file. This function supports macros that don't require parameters as well as macros that do require parameters.

Note: The Excel file must already exist, must contain the macro to be run, and must be macro-enabled.

Parameters

Parameter	Input Type	Description
full path	text	The full path (including file name) of the Excel file
macro name	Text	The name of the macro in the Excel file to run
Visible	Boolean	Whether or not to make Excel visible while the function is performed. If set to False, Excel is not made visible.
parameters	list of text	A text list that lists all parameters. The list must be of type text, even if the parameter values are of another type. The parameter values must be listed in the order they are expected by the macro. The number of elements in the list variable must exactly match the number of parameters expected by the macro or the macro will not run. If the macro does not expect any parameters, specify an empty list. From the list of variables, select Empty List of Text.

Returns

Nothing.

Example

The sample file BMI_Report.xlsm records a patient's Height and Weight, and also calculates their BMI (Body Mass Index), which is calculated Weight/(Height^2).

The file includes two macros:

Macro Name	Description	Parameters
InsertHeadings	Writes column headings into the cells A1 to C1 on sheet BMI.	None
BMICalculator	Writes in the values provided for height and weight, and the calculated BMI value.	height, weight

After running both macros, the workbook is populated as below:

The workflow below runs the first macro, InsertHeadings. Note that Parameters is set to Empty List of Text as this macro does not expect any parameters.

The second action in the same workflow step runs the second macro, BMICalculator. The parameter values for Height and Weight are specified in a temporary list.

When executed, nothing visible happens. However, when opening the Excel file after the workflow has ended, the specified data has been added to the sheet.

Note that the above implementation (running two macros from the same workflow step) is not considered the best practice as there is no verification that the first macro ended successfully before the second macro is run.

Set Cell Value in Active Excel

Sets the value of a cell in a specified sheet of the active Excel file.

Parameter

Parameter	Input Type	Description
sheet name	text	The name of the sheet in the Excel file
column	text	The column of the cell in which to set the value, specified by its letter, for example, C
row	text	The row of the cell in which to set the value, for example, 3
value	text	The value to write to the cell

Return

Nothing

Example

The sample file includes the following table on the sheet Sales by Salesperson.

The workflow below sets the value of the cell B4 to 100.

When executed, the value of the cell is set as expected.

Set Cell Value in Specified Excel

Sets the value of a cell in a specified sheet of the specified Excel file.

Parameter

Parameter	Input Type	Description
full path	text	The full path (including file name) of the Excel file
sheet name	text	The name of the sheet in the Excel file
column	text	The column of the cell in which to set the value, specified by its letter, for example, C
row	text	The row of the cell in which to set the value, for example, 3
value	text	The value to write to the cell

Return

Nothing

Example

The sample file includes the following table on the sheet Sales by Salesperson.

The workflow below sets the value of the cell B4 to 100.

When executed, the value of the cell is set as expected.

Set Multiple Cell Values in Active Excel

Sets the values of multiple cells in a specified sheet of the active Excel file. You can set the values of up to 256 cells in a single action call.

Parameters

Parameter

Input Type

Description

sheet name

text

The name of the sheet in the Excel file

value

text

A string specifying the address and value of each cell to be set. Separate the cell address and value using ||. Separate each address-value pair using |||, as below:

CellAddress||CellValue|||CellAddress||CellValue

Returns

Nothing.

Example

The sample file includes the following table on the sheet Sales by Region.

The following additional data is to be added below the existing data:

International	43	78	42	79	103	138
Internet	4	8	18	45	78	98

The workflow below adds this data starting from cell A8.

The value parameter is set as follows to specify the data as shown above.

A8||International|||B8||43|||C8||78|||D8||42|||E8||79|||F8||103|||G8||138|||A9||Internet|||B9||4|||C9||8|||D9||18|||E9||45|||F9||78|||G9||98

When executed, the additional data is added to the sample file.

Set Multiple Cell Values in Specified Excel

Sets the values of multiple cells in a specified sheet in a specified Excel file. You can set the values of up to 256 cells in a single action call.

Parameters

Parameter

Input Type

Description

full path

text

The full path (including file name) of the Excel file

sheet name

text

The name of the sheet in the Excel file

value

text

A string specifying the address and value of each cell to be set. Separate the cell address and value using ||. Separate each address-value pair using |||, as below:

CellAddress||CellValue|||CellAddress||CellValue

Returns

Nothing.

Example

The sample file includes the following table on the sheet Sales by Region.

The following additional data is to be added below the existing data:

International	43	78	42	79	103	138
Internet	4	8	18	45	78	98

The workflow below adds this data starting from cell A8.

The value parameter is set as follows to specify the data as shown above.

A8||International|||B8||43|||C8||78|||D8||42|||E8||79|||F8||103|||G8||138|||A9||Internet|||B9||4|||C9||8|||D9||18|||E9||45|||F9||78|||G9||98

When executed, the additional data is added to the sample file.

MS Excel Functions - Without Excel

(Available from version 7.1 onwards.)

These functions do not require that Excel be installed on the machine on which the solution is being run. The function is performed without opening Excel and is therefore performed very quickly.

These functions are valid only for Excel files generated using Office 2007 onwards (i.e. that have .XLSX extensions).

The Excel file must not be open in Excel when one of these functions is to be performed.

Example Files

An individual example is provided for each function described below.

Supported Time and Date Formats

When reading data from an Excel sheet (see Get Excel Cell Value, Get Excel Column Values, Get Excel Range Values, and Get Excel Row Values), time and date data will only be read correctly for dates entered in the formats listed below.

Format	Example
dd-mmm-yyyy hh:mm AM/PM	05-Jan-2019 02:00 AM
hh:mm AM/PM	02:00 AM
[$-en-US]dddd, mmmm d, yyyy	Saturday, January 5, 2019
m/d/yyyy	1/5/2019
dd-mmm-yy	12-Jan-20

Time formats with a single hour digit (h) are not supported.

Functions

Append Rows to Excel

Adds rows with specified data from the first empty row of the specified sheet in an Excel file, and sets the data format of each column in the range populated.

Parameters

Parameter	Input Type	Description
full path	text	The full path (including file name) of the Excel file
sheet name	text	The name of the sheet in the Excel file
data format	list of rows	The data formats to apply to each column in the range Valid data formats are: datetime, decimal, number, string, bool. Note: Although this parameter is specified using a list of rows, only the first row will be used.
values	list of rows	The values to write to the cells in the range

Returns

Returns a Boolean value true if the operation was successful.

Example

This example adds a single row to Sheet 1 of the sample file. The row is as follows:

	A	B	C	D
Format	string	number	string	datetime
Data	Payment Terms (Days):	30	Latest Date:	2020-05-01

The workflow below populates two rows:

data_format_row_a: Holds the format values as per the table above. This row is added to the list of rows data_formats_a.
data_row_a: Holds the data values as per the table above. This row is added to the list of rows data_a.

The rows are then appended to the Excel file.

After execution, the row is appended to the Excel file.

Clear Excel Rows

Clears the contents of the specified range of rows from the specified sheet in the specified Excel file.

Parameters

Parameter	Input Type	Description
full path	text	The full path (including file name) of the Excel file
sheet name	text	The name of the sheet in the Excel file
start row	number	The number of the first row to clear
end row	number	The number of the last row to clear

Returns

Returns a Boolean value true if the operation was successful.

Example

This example clears the contents in rows 13 and 14 of this table on Sheet 1 of the sample file.

The workflow below clears the range of rows from 13 to 14 (inclusive). The result is stored in the Boolean variable Boolean_a.

When executed, the specified range of rows are cleared.

The variable Boolean_a is set to true, indicating that the operation was successful.

Delete Rows in Excel

Removes the specified range of rows from the specified sheet in the specified Excel file.

Parameters

Parameter	Input Type	Description
full path	text	The full path (including file name) of the Excel file
sheet name	text	The name of the sheet in the Excel file
start row	number	The number of the first row to delete
end row	number	The number of the last row to delete

Returns

Returns a Boolean value true if the operation was successful.

Example

This example removes rows 13 and 14 of this table on Sheet 1 of the sample file.

The workflow below removes the range of rows from 13 to 14 (inclusive). The result is stored in the Boolean variable Boolean_a.

When executed, the specified range of rows are removed.

The variable Boolean_a is set to true, indicating that the operation was successful.

Get Cell Formula in Excel

Retrieves the formula in a specified cell in a specified sheet in an Excel file.

Parameters

Parameter	Input Type	Description
full path	text	The full path (including file name) of the Excel file
sheet name	text	The name of the sheet in the Excel file
cell address	text	The address of the cell, in format A1

Returns

Returns the cell formula as text

Example

This example retrieves the formula in cell F16 on Sheet 1 of the sample file, shown below.

The workflow below retrieves the formula and stores it in the text variable get_excel_formula_response.

When executed, the variable get_excel_formula_response is set to the formula in the specified cell.

Get Cell Range Values in Excel

Retrieves the values of all cells in a specified range in a specified sheet in an Excel file.

Parameters

Parameter	Input Type	Description
full path	text	The full path (including file name) of the Excel file
sheet name	text	The name of the sheet in the Excel file
cell range	text	The cell range, specified in format A1:C15

Returns

Returns a list of rows containing the values of the cells in the range

Example

This example retrieves the contents of the cells in the range B12 to F14 from Sheet 1 of the sample file.

The workflow below retrieves the values and stores them in the list of rows get_excel_range_values_response.

When executed, the list of rows get_excel_range_values_response is set to the values of the cells in the specified range.

Get Cell Value in Excel

Retrieves the value of a specified cell in a specified sheet in an Excel file.

Parameters

Parameter	Input Type	Description
full path	text	The full path (including file name) of the Excel file
sheet name	text	The name of the sheet in the Excel file
cell address	text	The address of the cell, for example, A1

Returns

Returns the value of the specified cell as text.

Example

This example retrieves the value of the cell F16 from Sheet 1 of the sample file.

The cell to retrieve is shown below:

The workflow below retrieves the value of cell F16 and stores it in the text variable get_cell_value_response.

When executed, the value of get_cell_value_response is set to 738.68.

Get Column Count in Excel

Counts the number of columns in the range starting at the first non-empty column and ending at the last non-empty column, inclusive, in the specified sheet in the specified Excel file.

Parameters

Parameter	Input Type	Description
full path	text	The full path (including file name) of the Excel file
sheet name	text	The name of the sheet in the Excel file
ignore empty columns	Boolean	Set to True to exclude empty columns from the column count, or to False to include them.
columns have headers	Boolean	Set to True if the table has headers, or False if it does not. If set to True and ignore empty columns is set to True, then an empty column will be excluded from the column count even though it has a value in the header row.

Returns

Returns a number indicating the number of columns on the specified sheet.

Example

Sheet 3 of the sample file includes this table. Note that the table has headers. Column D is empty (apart from its header).

The workflow below counts the number of columns on the sheet. Ignore empty columns is set to True, so empty columns will not be included in the count. The result is stored in the number variable number.

When executed, the value of the variable number is set to 6, as there are 6 non-empty columns.

The workflow below is modified so that Ignore empty columns is set to False, so empty columns will be included in the count.

When executed, the value of the variable number is set to 7, as there are 7 columns including empty columns.

Get Column Values in Excel

Retrieves the values of all cells in a single column in a specified sheet in an Excel file, from a specified cell downwards.

Parameters

Parameter	Input Type	Description
full path	text	The full path (including file name) of the Excel file
sheet name	text	The name of the sheet in the Excel file
cell address	text	The address of the top cell of the column, specified in the format A1. If set to A5, for example, will return all values in column A from row 5 downward.

Returns

Returns the column's cell values in a text list.

Example

This example retrieves all values in column F from row 12 downward, as shown below.

The workflow below retrieves the values and stores them in the text list get_excel_col_vals_response.

When executed, the values of the cells are stored in the text list get_excel_col_vals_response.

Get Number of First Empty Row in Excel

(Available from 7.4)

Retrieves the row number of the first empty row in the specified sheet in the specified Excel file.

Parameters

Parameter	Input Type	Description
full path	text	The full path (including file name) of the Excel file
sheet name	text	The name of the sheet in the Excel file

Returns

Returns the row number as a number.

Example

The sample file includes the following table on Sheet 3.

The workflow below retrieves the row number of the first empty row on this sheet and stores it in the number variable number.

When executed, the workflow sets number to 6. Row 6 is the first empty row in the sheet, although there are populated rows below it.

Get Number of First Empty Row at Bottom of Excel

(Available from 7.4)

Retrieves the row number of the row below the last populated row in the specified sheet in the specified Excel file.

Parameters

Parameter	Input Type	Description
full path	text	The full path (including file name) of the Excel file
sheet name	text	The name of the sheet in the Excel file

Returns

Returns the row number as a number.

Example

The sample file includes the following table on Sheet 3.

The workflow below retrieves the row number of the first empty row below the last populated row on this sheet and stores it in the number variable number.

When executed, the workflow sets number to 8. Since row 7 is the last populated row on the sheet, the workflow returned the row number of the row below that.

Get Row Values in Excel

Retrieves the values of all cells in a single row in a specified sheet in an Excel file, from a specified cell rightwards.

Parameters

Parameter	Input Type	Description
full path	text	The full path (including file name) of the Excel file
sheet name	text	The name of the sheet in the Excel file
cell address	text	The address of the left-most cell of the row, specified in the format C1. If set to C5, for example, will return all values in row 5 from column C rightward.

Returns

Returns a list of text containing the cell values

Example

This example retrieves all values in row 12 from column B rightward in Sheet 1 of the sample file, as shown below.

The workflow below retrieves the values and stores them in the text list get_excel_row_values_response.

When executed, the values of the cells are stored in the text list get_excel_row_values_response.

Get Table Range in Excel

Retrieves the table range of an Excel table with a specified name.

Parameters

Parameter

Input Type

Description

full path

text

The full path (including file name) of the Excel file

sheet name

text

The name of the sheet in the Excel file

name of table

text

The name of the table in the Excel file

See the example for instructions on how to define a table in Excel.

Returns

Returns the range as text, in the format A1:C5.

Example

This example retrieves the table range of the table named SaleItems from the sample file.

The table is shown below. Note that it spans cells B11 to F16.

The workflow below retrieves the range of the table and stores it in the variable table_range_response.

When executed, the value of table_range_response is set to B11:F16.

Insert Empty Row in Excel

Inserts a new row on a specified sheet in a specified Excel file below a specified row.

Parameters

Parameter	Input Type	Description
full path	text	The full path (including file name) of the Excel file
sheet name	text	The name of the sheet in the Excel file
after row	number	The number of the row under which to insert the new row

Returns

Returns a number indicating the row number of the inserted row.

Example

This workflow inserts a new row below row 14 on Sheet 1 of the sample file. The result is stored in the number variable number.

When executed, the new row is inserted in the Excel file.

The variable number is set to 15 because the new row is at row 15.

Set Cell Value in Excel

Sets the value of a specified cell on a specified sheet in an Excel file.

Parameters

Parameter	Input Type	Description
full path	text	The full path (including file name) of the Excel file
sheet name	text	The name of the sheet in the Excel file
cell address	text	The address of the cell, specified in format A1

Returns

Returns a Boolean value true if the operation was successful.

Example

In this example, the cell at B8 on Sheet 1 of the sample file is set to Email, and the cell at C8 is set to accounts@royal-service.com. The Boolean variables Boolean_b and Boolean_c are used to indicate success or failure.

The cells are populated as expected.

Set Celll Range Values in Excel

Sets the values of all cells in a specified range on a specified sheet in an Excel file, and sets the data format for each column in the range.

The values provided must match the formats specified or the operation will fail.

Parameters

Parameter	Input Type	Description
full path	text	The full path (including file name) of the Excel file
sheet name	text	The name of the sheet in the Excel file
cell range	text	The cell range, specified in format A1:C15 If the size of the range does not match the size of the data supplied, the range will be ignored and the data supplied will be written starting from the top left cell of the specified range.
data format	list of rows	The data formats to apply to each column in the range Valid data formats are: datetime, decimal, number, string, bool. Note: Although this parameter is specified using a list of rows, only the first row will be used.
values	list of rows	The values to write to the cells in the range

Returns

Returns a Boolean value true if the operation was successful.

Example

This example adds multiple rows to Sheet 2 of the sample file. The data is as follows:

	A	B	C
Format	datetime	string	decimal
Row 1	1 January 2020	Purchase	4131.21
Row 2	1 February 2020	Refund	-50.32

The workflow below populates the following rows and lists of rows:

data_formats_row_b: Holds the format values as per the table above. This row is added to the list of rows data_formats_b.
data_row_b, data_row_c: Hold the data values in the rows as per the table above. The rows are added to the list of rows data_b.

The rows are then written to the Excel file. The Boolean variable Boolean_d is set to indicated success or failure.

After execution, the rows are added to the Excel file.

The Boolean value Boolean_d is set to true, indicating success.

Set Cell Range Values and Headers in Excel

Sets the values of all cells in a specified range on a specified sheet in an Excel file, sets the data format for each column in the range, and defines range column headers.

The values provided must match the formats specified or the operation will fail.

Parameters

Parameter	Input Type	Description
full path	text	The full path (including file name) of the Excel file
sheet name	text	The name of the sheet in the Excel file
cell range	text	The cell range, specified in format A1:C15 If the size of the range does not match the size of the data supplied, the range will be ignored and the data supplied will be written starting from the top left cell of the specified range.
data format	list of rows	The data formats to apply to each column in the range Valid data formats are: datetime, decimal, number, string, bool. Note: Although this parameter is specified using a list of rows, only the first row will be used.
values	list of rows	The values to write to the cells in the range
headers	list of rows	The headers to apply to the columns in the range Note: Although this parameter is specified as a list of rows, only the contents of the first row will be used for headers, the other rows will be ignored.

Returns

Returns a Boolean value true if the operation was successful

Example

This example adds a row of data with headings to Sheet 3 of the sample file. The data is as follows:

	A	B	C
Format	datetime	string	decimal
Heading	Date	Event	Amount
Row 1	1 January 2020	Purchase	4131.21

The workflow below populates the following rows and lists of rows:

data_formats_row_d: Holds the format values as per the table above. This row is added to the list of rows data_formats_d.
data_headings_row: Holds the row headings as per the table above. The row is added to the list of rows data_headings.
data_row_e: Holds the data values in the row as per the table above. The row is added to the list of rows data_e.

The heading and row is then written to the Excel file. The Boolean variable Boolean_e is set to indicated success or failure.

After execution, the data is written to the Excel file.

The Boolean value Boolean_e is set to true, indicating success.

Best Practices

Below are best practices related to the MS Excel library functions.