EXCEL

Articles

Open Excel

The Open excel feature is used to Open a specified excel. Note: Make sure that the selected excel sheet is available in the specified machinecluster. If the selected excel file is not available in the specified folder, then a new excel file is created and opened.

Syntax

Open Excel(path)

Sample

Open Excel (C:TrainingExcel.xls)
Open Excel (C:TrainingExcel.xlsx)
Open Excel (C:TrainingExcel.xlsm)
Open Excel (C:TrainingExcel.csv)

Parameter Details

Input Parameter Name	Description
path (required)	Local file path of the excel file to be used for automation

Open Protected Excel

The Open Protected Excel feature is used to open a password protected excel of the following format: xls, xlsx, xlsm, .csv files.

Syntax

Open Protected Excel (path, password)

Sample

Open Protected Excel (D:InvoiceDetailsInvoice1, …….)

Parameter Details

Input Parameter Name	Description
path (required)	Local file path of the excel file to be used for automation
Password (required)	Password to open the secured excel file

Select Sheet By Name

The Select Sheet By Name command is used to select a sheet by the specified name.

Syntax

Select Sheet By Name(sheet_name)

Sample

Select Sheet By Name (Sheet1)
Select Sheet By Name (BankReconciliation)

Parameter Details

Input Parameter Name	Description

sheet_name (required) Excel sheet name to be selected.

Select Sheet By Index

The Select Sheet By Index command is used to select a sheet by the specified index number. This function must be used after the ‘Open Excel / Open Protected Excel’ command.

Syntax

Select Sheet By Index(index)

Sample

Select Sheet By Index (1)
Select Sheet By Index (4)

Parameter Details

Input Parameter Name	Description
index (required)	Excel sheet index to be selected. Index starts from ‘1’

Close File

Use this excel command to close the opened excel file along with the excel instance.

Syntax

Close File (path)

Sample

Close File (C:TrainingExcel.xls)

Parameter Details

Input Parameter Name	Description
path	Path of the file to be closed

Close Active File

Use this excel command to close the active opened excel workbook, without closing the excel instance.

Syntax

Close Active File ()

Sample

Close Active File ()

Parameter Details

Input Parameter Name	Description
-	-

Close Excel

Use this excel command to close the opened excel instance.

Syntax

Close Excel()

Sample

Close Excel()

Parameter Details

Input Parameter Name	Description
-	-

Paste Value

Use this excel command to paste the values from the output result of ‘Copy Value’ excel command

Syntax

Paste Value(input_xml)

Sample

Paste Value(copiedValues)
Paste Value(outputVariable)

Parameter Details

Input Parameter Name	Description
input_xml (required)	Xml output from the Copy Value return value.

Copy Metadata

Use this excel command to copy the metadata (copies value along with its format like, font, colour etc.,) of the specified range of cells in the opened excel.

Syntax

Copy MetaData (range)

Sample with hard coded inputs

Copy MetaData (B5:F17)

Sample with inputs through variables

In case, if the number of rows are dynamic, then, use Get Last Row with Data expression to get the last row. After having the last row, use the concat expression to get the range in the above format.

Get Last Cell Value = lastCellValueVariable
Concat (lastCellValueVariable , A1) = rangeVariable (rangeVariable will look like, say, A1:E7)

Parameter Details

Input Parameter Name	Description
range (required)	Range of cells within the excel sheet (Same as Copy Value)

Paste Metadata

Use this excel command to paste the copied range of data into a data table.

Syntax

Paste From Table (table, cell)

Sample

Paste From Table (Invoice_Details_DataTable, G1)

Parameter Details

Input Parameter Name	Description
table (required)	Data table defined as the output variable in the excel node
cell (required)	Start point of the excel cell address from which the data must be pasted into the active excel sheet

Copy To Clipboard

Use this excel command to copy the contents and the metadata of the given range of cells or a single cell to clipboard.

Syntax

Copy To Clipboard (select_range)

Sample with hard coded inputs

Copy To Clipboard (C4)
Copy To Clipboard C4:F8)

Sample with inputs through variables

Get Last Cell Value = lastCellValueVariable
Concat (lastCellValueVariable , A1) = rangeVariable (rangeVariable will look like, say, A1:E7)

Parameter Details

Input Parameter Name	Description
select_range(required)	Single / Multiple cell range to be copied to clipboard

Paste Value From Clipboard

Use this excel command to paste the clipboard values from the selected cells of the active sheet from the opened excel file(* Has to be used after ‘Copy To Clipboard’ excel command).

Syntax

Paste Value From Clipboard (paste_range)

Sample

Paste Value From Clipboard (C4)
Paste Value From Clipboard (C4:F8)

Parameter Details

Input Parameter Name	Description
paste_range	Single / Multiple cell range can be pasted from the clipboard

Paste MetaData From Clipboard

Use this excel command to paste the metadata from clipboard of the current excel file from the output result of ‘Copy To Clipboard’ excel command.

Syntax

Paste MetaData From Clipboard(range)

Sample

Paste MetaData From Clipboard (A1:E5)
Paste MetaData From Clipboard (outputVariable)

Parameter Details

Input Parameter Name	Description

range (required) Range of cells for the metadata to be pasted

Delete Sheet By Name

Use this excel command to delete the selected sheet through the input name of the sheet.

Syntax

Delete Sheet By Name(sheet_name)

Sample

Delete Sheet By Name (Sheet1)
Delete Sheet By Name (BankReconciliation)

Parameter Details

Input Parameter Name	Description
sheet_name (required)	Excel sheet name to be deleted.

Delete Sheet By Index

Use this excel command to delete the selected sheet through the input index.

Syntax

Delete Sheet By Index (index)

Sample

Delete Sheet By Index(1)
Delete Sheet By Name (BankReconciliation)

Parameter Details

Input Parameter Name	Description
index (required)	Excel sheet index to be deleted. Index starts from ‘1’.

Save File

Use this excel command to save the opened file. This must be mandatorily used after the ‘Open File’ command is used.

Syntax

Save File()

Sample

Save File()

Parameter Details

Input Parameter Name	Description
-	-

Save File As

Use this excel command to save the opened file in a different name or different location.

Syntax

Save File As (new_path)

Sample

Save File As (C:TrainingNewExcel1.xlsx)

Parameter Details

Input Parameter Name	Description
new_path (required)	Path of the file to be saved.

Update Cells

Use this excel command to update the values of the cells in the active opened excel file.

Syntax

Update Cells (update_range, update_value)

Sample

Update Cells (A1, test)
Update Cells(A1:G9, array[])

Parameter Details

Input Parameter Name	Description
update_range (required)	Cell range to be updated in the selected excel sheet.
	(Single or multiple cell range can be provided)
update_value (required)	Values to be updated within the given range

Clear Range

Use this excel command to clear values in specific range of cells in an opened excel file.

Syntax

Clear Range (clear_range)

Sample

Clear Range (D1)
Clear Range (B1:G9)

Parameter Details

Input Parameter Name	Description
clear_range (required)	Cell range that needs data to be cleared in the selected excel sheet.
	(Single or Multiple cell range can be provided)

Delete Range

Use this excel command to delete the range of cells from the active sheet of the opened excel file.

Syntax

Delete Range (delete_range)

Sample

Delete Range (C4)
Delete Range (C4:F8)

Parameter Details

Input Parameter Name	Description
Delete_range (required)	Single / Multiple cell range can be deleted from the active excel sheet.

Send keys

Use this excel command to send the special keys to the active sheet in an opened excel file.

Syntax

Send keys (key_string)

Sample

Send keys (TAB)
Send keys (F2)

Parameter Details

Input Parameter Name	Description
key_string(required)	Used to send the special keys to the active sheet in an opened excel file.

Insert Column

Use this excel command to insert a column in to the active sheet of the opened excel file.

Syntax

Insert Column (column_letter)

Sample

Insert Column (D)
Insert Column (AB)

Parameter Details

Input Parameter Name	Description

Column_letter (required) Column letter to be added into the active sheet.

Delete Column

Use this excel command to delete a column from the active sheet of the opened excel file.

Syntax

Delete Column(Column_letter)

Sample

Delete Column(A)
Delete Column (AB)

Parameter Details

Input Parameter Name	Description
Column_letter (required)	Column letter to be deleted into the active sheet.

Insert Row

Use this excel command to insert a row into the active sheet of the opened excel file.

Syntax

Insert Row (row_number)

Sample

Insert Row (3)
Insert Row (45)

Parameter Details

Input Parameter Name	Description
row_number(required)	Row number to be added into the active sheet.

Delete Row

Use this excel command to delete the specific row from the active sheet of the opened excel file.

Syntax

Delete Row (row_number)

Sample

Delete Row (4)
Delete Row (121)

Parameter Details

Input Parameter Name	Description
row_number (required)	Row number to be deleted from the active sheet of the opened excel file.

Get Cell Value

Use this excel command to get the value of the cell from the active excel sheet with the address provided.

Syntax

Get Cell Value (cell_address) = value

Sample

Get Cell Value (G1) = 1234

If the value needs to be passed to the next application / node, then

Step1 : An output variable, ‘output_variable’ has to be defined

Step2: Get Cell Value (G1) = output_variable

If the value needs to be used locally within the node, then

Step1: At Edit UI window, a local variable, ‘local_variable’ has to be defined like, Set Variable(local_variable) = 0 Step2. Get Cell Value (G1) = local_variable

Parameter Details

Input Parameter Name	Description
cell_address (required)	Cell address of the active excel sheet

Get Last Row With Data

Use this excel command to get the last row number with values in the active sheet of the open excel file.

Syntax

Get Last Row With Data () = lastRowNumber

Sample

Get Last Row With Data () = 4*

If the value needs to be passed to the next application / node, then

Step1 : An output variable, ‘output_variable’ has to be defined Step2 : At Edit UI Window, Get Last Row With Data () = output_variable

If the value needs to be used locally within the node, then

Step1: At Edit UI window, a local variable, ‘local_variable’ has to be defined. Step2. Get Last Row With Data () = local_variable

Parameter Details

Input Parameter Name	Description
-	-

Get Last Column with Data

Use this excel command to get the last column number with values in the active sheet of the open excel file.

Syntax Get Last Column With Data () = lastColumnNumber

Sample 1. Get Last Column With Data () = 5*

If the value needs to be passed to the next application / node, then

Step1 : An output variable, ‘output_variable’ has to be defined Step2 : At Edit UI Window, Get Last Column with Data () = output_variable

If the value needs to be used locally within the node, then

Step1: At Edit UI window, a local variable, ‘local_variable’ has to be defined. Step2. Get Last Column with Data () = local_variable

Parameter Details

Input Parameter Name	Description
-	-

Get Last Row And Column with Data

Use this excel command to get last row and column number in the active sheet of the open excel file.

Syntax

Get Last Row And Column With Data () = last_row_number, last_col_number

Sample

Get Last Row And Column With Data () = 3,4

If the value needs to be passed to the next application / node, then

Step1 : An output variable, ‘output_variable’ has to be defined Step2 : At Edit UI Window, Get Last Row And Column With Data () = output_variable

If the value needs to be used locally within the node, then

Step1: At Edit UI window, a local variable, ‘local_variable’ has to be defined. Step2. Get Last Row And Column With Data () = local_variable

Parameter Details

Input Parameter Name	Description
-	-

Convert Col Letter To Number

Use this excel command to convert the column letter to the column number in the active excel sheet of the open excel file.

Syntax

Convert Col Letter To Number (col_letter) = columnNumber

Sample

Convert Col Letter To Number (G) = 7*
Convert Col Letter To Number (AA) = 26*

If the value needs to be passed to the next application / node, then

Step1 : An output variable, ‘output_variable’ has to be defined Step2 : At Edit UI Window, Convert Col Letter To Number (G) = output_variable

If the value needs to be used locally within the node, then

Step1: At Edit UI window, a local variable, ‘local_variable’ has to be defined. Step2. Convert Col Letter To Number (AA) = local_variable

Note : The above logic is same for input variables as well, in this case G / AA.

Parameter Details

Input Parameter Name	Description
col_letter(required)	Column letter of the active excel sheet

Convert Col No to Letter

Use this excel command to convert the column number to the column letter in the active excel sheet of the open excel file.

Syntax

Convert Col No to Letter (col_no) = columnLetter*

Sample

Convert Col No to Letter (7) = G*
Convert Col No to Letter (26) = AA*

If the value needs to be passed to the next application / node, then

Step1 : An output variable, ‘output_variable’ has to be defined Step2 : At Edit UI Window, Convert Col No to Letter (7) = output_variable

If the value needs to be used locally within the node, then

Step1: At Edit UI window, a local variable, ‘local_variable’ has to be defined. Step2. Convert Col No to Letter (26) = local_variable

Note : The above logic is same for input variables as well, in this case 7 / 26.

Parameter Details

Input Parameter Name	Description
col_no (required)	Column number of the active excel sheet

Rename Sheet By Name

Use this excel command to rename a existing sheet within an open excel file.

Syntax

Rename Sheet By Name(existing_name, new_name)

Sample

Rename Sheet By Name (Sheet1, Invoice_Details)

Parameter Details

Input Parameter Name	Description
existing_name (required)	Existing name of the sheet from the open excel file
new_name (required)	New name of the excel sheet.

Rename Sheet By Index

Use this excel command to rename a existing sheet name using sheet index within an open excel file.

Syntax

Rename Sheet By Index(index, new_name)

Sample

Rename Sheet By Index (1 , Invoice_Details)

Parameter Details

Input Parameter Name	Description
cell_address (required)	Excel sheet index to be renamed. Index starts from ‘1’.
new_name (required)	New name of the sheet from the open excel file.

Paste From Table

Use this excel command to paste the copied range of data into a data table.

Syntax

Paste From Table (table, cell)

Sample

Paste From Table (Invoice_Details_DataTable, G1)

Parameter Details

Input Parameter Name	Description
table (required)	Data table defined as the output variable in the excel node
cell (required)	Start point of the excel cell address from which the data must be pasted into the active excel sheet

Run Macro

Use this excel command to execute a macro defined from the opened excel file.

Syntax

Run Macro (macro_name)

Sample

Run Macro (Filter Duplicate Invoices)

Parameter Details

Input Parameter Name	Description
macro_name (required)	Name of the macro to be executed.

Remove Module

Use this excel command to delete an excel macro module from the opened excel file.

Syntax

Remove Module (module_name)

Sample

Remove Module (Filter Duplicate Invoices)

Parameter Details

Input Parameter Name	Description
Module_name (required)	Name of the module to be deleted

Create Table

The Create Table excel feature creates an empty table in the selected sheet within a specified range.

Syntax

Create Table (range,table_name)

Sample

Create Table (A1:F10, New_Table)

Parameter Details

Input Parameter Name	Description
range	Range of the cells to create a table.
table_name	Name of the table to be created.

Filter Table

The Filter Table excel feature filters the data of a selected table and column, based on the value specified in the filter_options field.

Syntax

Filter Table (table_name, column_name, value)

Sample

Filter Table (New_Table, Age, >5)

Parameter Details

Input Parameter Name	Description
table_name	Name of the table to filter.
column_name	Name of the column to be filtered.
filter_options	Condition to do the filter operation.

Sort Table

The Sort Table excel feature sorts the data of a selected table and column, in a selected order (ascending or descending).

Syntax

Sort Table (table_name, column_name, sort_order)

Sample

Sort Table (New_Table, Age, Ascending)

Parameter Details

Input Parameter Name	Description
table_name	Name of the table to Sort.
column_name	Name of the column to be Sorted.
sort_order	Condition to do the sort operation. This can be Ascending or Descending

Read Cell Formula

The Read Cell Formula excel feature reads the cell formula of the specified cell and displays the output (formula) in the Result of Execution screen.

Syntax

Read Cell Formula (cell range)

Sample

Read Cell Formula (B2)

Parameter Details

Input Parameter Name	Description
range	Range of the cell to get the formula of.

Write Cell Formula

The Write Cell Formula excel feature displays the output of a given formula in a specified destination cell.

Syntax

Write Cell Formula (formula, cell)

Sample

Write Cell Formula (A1/D2,B20)

Parameter Details

Input Parameter Name	Description
formula	Formula to write.
cell	Destination cell in which the output must be displayed.

Set Range Colour

The Set Range Colour excel feature sets the required colour to the cells within a specified range.

Syntax

Set Range Colour (range,color)

Sample

Set Range Colour (A1:B10, 0,0,255)

Parameter Details

Input Parameter Name	Description
range	Cell range to apply the color.
color	RGB value of the color apply

Get Cell Colour

The Get Cell Colour excel feature reads the colour of the specified cell and displays the cell colour in RGB format in the Result of Execution dialog box.

Syntax

Get Cell Colour (cell)

Sample

Get Cell Colour (A1)

Parameter Details

Input Parameter Name	Description
cell	Cell to read the color.

Remove Duplicates

The Remove Duplicates excel feature removes the duplicate data from the selected table.

If there are multiple rows in a selected table, it checks the data row-wise and if there are multiple columns with a single row it checks the data column-wise.

Syntax

Remove Duplicates (table_range, header_included)

Sample

Remove Duplicates (A1:B10, TRUE)

Parameter Details

Input Parameter Name	Description
table_range	Table range to remove duplicates.
header_included	Whether or not to include the headers. This can be TRUE or FALSE

If True option selected from the header_included field, then the data in the table header is considered and if it is False**, the data in the table header is not considered.

Copy Paste Range

The Copy Paste Range excel feature is implemented to copy and paste the data from the selected cells to destination cells.

Syntax

Copy Paste Range (source_sheet, source_range, destination_sheet, destination_cell, paste_type)

Sample

Copy Paste Range (data, A1:B10, new, A1:B10, formula)

Parameter Details

Input Parameter Name	Description
source_sheet	Sheet to copy data from.
source_range	Range in the source sheet to copy the data from.
destination_sheet	Sheet to paste the data to.
destination_cell	Range in the destination sheet to paste the data to.
paste_type	There are four different options for paste_type field:
	1. Value: Can copy and paste both numbers and values.
	2. Number format: Can copy and paste both numbers and values.
	3. Cell format: Copies along with cell format (font, colour).
	4. Formula: Copies along with the formula specific to the cell.

Lookup Range

The Lookup Range excel feature searches for the specified value within a lookup range and to display the destination cell position. Syntax

Lookup Range (lookup_range, lookup_value, occurance)

Sample

Lookup Range (A1:B10, key, 3)

Parameter Details

Input Parameter Name	Description
lookup_range	Range to search data from.
lookup_value	Value to search
occurance	Number of times the value occurs.

Get Table Range

The Get Table Range excel feature displays the cell range of the selected table.

Syntax

Get Table Range (table_name)

Sample

Get Cell Colour (data)

Parameter Details

Input Parameter Name	Description
table_name	Name of the table to get the range.

Auto Fill

The Auto Fill excel feature allows the user to extend the data of a selected cell to the specified range of cells in a series.

Syntax

Auto Fill (source_range, extend_till_cell)

Sample

Auto Fill (A1, A1:B10,)

Parameter Details

Input Parameter Name	Description
source_range	Range to get the data from.
extend_till_cell	Cell range till which the data must be filled.

Clear Filter

The Clear Filter excel feature allows the user to clear the filter that is applied to the selected table.

Syntax

Clear Filter (table_name)

Sample

Get Cell Colour (data)

Parameter Details

Input Parameter Name	Description
table_name	Name of the table to cear the filter.

Is Sheet Exists

The Is Sheet Exists excel feature allows the user to clear the filter that is applied to the selected sheet.

Syntax

Is Sheet Exists (sheet_name)

Sample

Is Sheet Exists (sheet2)

Parameter Details

Input Parameter Name	Description
sheet_name	Name of the sheet to find.

If the sheet exists, it returns True as output in the Result of Execution screen. Else, it returns False.

Refresh Pivot Table

The Refresh Pivot Table excel feature refreshes the selected pivot table.

Syntax

Refresh Pivot Table (pivot_table_name)

Sample

Refresh Pivot Table (table3)

Parameter Details

Input Parameter Name	Description
pivot_table_name	Name of the pivot table to refresh.

Copy To Table

The Copy To Table excel feature is used to copy a range of cell data to a defined data table (includes metadata).

Syntax

Copy To Table (table_name, range_value)

Sample

Copy To Table (data, A1:B10, new)

Parameter Details

Input Parameter Name	Description
table_name	Name of the table to copy the data.
range_value	Range of cells that must be copied to the table.

Copy To Table Without MetaData

The Copy To Table Without MetaData excel feature is used to copy a range of cell data to a defined data table (does not includes metadata).

Syntax

Copy To Table Without MetaData (table_name, range_value)

Sample

Copy To Table Without MetaData (data, A1:B10, new)

Parameter Details

Input Parameter Name	Description
table_name	Name of the table to copy the data.
range_value	Range of cells that must be copied to the table.

Bulk Copy To Table

The Bulk Copy To Table excel feature is used to bulk copy a range of cell data from a specified sheet to a defined data table without cell metadata.

Syntax

Bulk Copy To Table (table_name, range_value, sheet_name)

Sample

Bulk Copy To Table (data, A1:B10, new)

Parameter Details

Input Parameter Name	Description
table_name	Name of the table to copy the data.
range_value	Range of cells that must be copied to the table.
sheet_name	Name of the excel sheet from which the data must be copied.

Copy Value

The Copy Value excel feature is used to copy a specific range of cells.

Syntax

Copy Value (range)

Sample

Copy Value (A1:B10)

Parameter Details

Input Parameter Name	Description
range	Range to copy the data.

Create Sheet

The Create Sheet excel feature is used to copy a specific range of cells.

Syntax

Create Sheet (sheet_name)

Sample

Create Sheet (data)

Parameter Details

Input Parameter Name	Description
sheet_name	Name of the sheet to create.

Get Last Cell with Data

The Get Last Cell with Data excel feature is used to get the last cell address with data.

Syntax Get Last Cell With Data () = lastCellNumber

Sample 1. Get Last Cell With Data () = 4*

If the value needs to be passed to the next application / node, then

Step1 : An output variable, ‘output_variable’ has to be defined Step2 : At Edit UI Window, Get Last Cell With Data () = output_variable

If the value needs to be used locally within the node, then

Step1: At Edit UI window, a local variable, ‘local_variable’ has to be defined. Step2. Get Last Cell With Data () = local_variable

Parameter Details

Input Parameter Name	Description
-	-

Search

Articles

Open Excel

Open Protected Excel

Select Sheet By Name

Select Sheet By Index

Close File

Close Active File

Close Excel

Paste Value

Copy Metadata

Paste Metadata

Copy To Clipboard

Paste Value From Clipboard

Paste MetaData From Clipboard

Delete Sheet By Name

Delete Sheet By Index

Save File

Save File As

Update Cells

Clear Range

Delete Range

Send keys

Insert Column

Delete Column

Insert Row

Delete Row

Get Cell Value

Get Last Row With Data

Get Last Column with Data

Get Last Row And Column with Data

Convert Col Letter To Number

Convert Col No to Letter

Rename Sheet By Name

Rename Sheet By Index

Paste From Table

Run Macro

Remove Module

Create Table

Filter Table

Sort Table

Read Cell Formula

Write Cell Formula

Set Range Colour

Get Cell Colour

Remove Duplicates

Copy Paste Range

Lookup Range

Get Table Range

Auto Fill

Clear Filter

Is Sheet Exists

Refresh Pivot Table

Copy To Table

Copy To Table Without MetaData

Bulk Copy To Table

Copy Value

Create Sheet

Get Last Cell with Data