expressions

EXPRESSIONS REPOSITORY

Expressions - Excel

01. 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 machine\cluster. 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
1. Open Excel (C:\\Training\\Excel.xls)
2. Open Excel (C:\\Training\\Excel.xlsx)
3. Open Excel (C:\\Training\\Excel.xlsm)
4. Open Excel (C:\\Training\\Excel.csv)


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

02. 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
1. Open Protected Excel (D:\\InvoiceDetails\Invoice1, .......)


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

03. 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
1. Select Sheet By Name (Sheet1)
2. Select Sheet By Name (BankReconciliation)


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

04. 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
1. Select Sheet By Index (1)
2. Select Sheet By Index (4)


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

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

Syntax
Close File (path)

Sample
1. Close File (C:\\Training\\Excel.xls)


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

06. 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
- -

07. Close Excel
Use this excel command to close the opened excel instance.

Syntax
Close Excel()

Sample
Close Excel()


Parameter Details
Input Parameter Name Description
- -

08. 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
1. Paste Value(copiedValues)
2. Paste Value(outputVariable)


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

09. 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)

10. 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

11. 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
1. Copy To Clipboard (C4)
2. Copy To Clipboard C4:F8)

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
select_range(required) Single / Multiple cell range to be copied to clipboard

12. 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
1. Paste Value From Clipboard (C4)
2. Paste Value From Clipboard (C4:F8)


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

13. 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
1. Paste MetaData From Clipboard (A1:E5)
2. Paste MetaData From Clipboard (outputVariable)


Parameter Details
Input Parameter Name Description
range (required) Range of cells for the metadata to be pasted

14. 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
1. Delete Sheet By Name (Sheet1)
2. Delete Sheet By Name (BankReconciliation)


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

15. Delete Sheet By Index
Use this excel command to delete the selected sheet through the input index.

Syntax
Delete Sheet By Index (index)

Sample
1. Delete Sheet By Index(1)
2. Delete Sheet By Name (BankReconciliation)


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

16. 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
- -

17. 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
1. Save File As (C:\\Training\\NewExcel1.xlsx)


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

18. 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
1. Update Cells (A1, test)
2. 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

19. 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
1. Clear Range (D1)
2. 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)

20. 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
1. Delete Range (C4)
2. 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.

21. 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
1. Send keys (TAB)
1. 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.

22. 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
1. Insert Column (D)
2. Insert Column (AB)


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

23. 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
1. Delete Column(A)
2. Delete Column (AB)


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

24. 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
1. Insert Row (3)
2. Insert Row (45)


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

25. 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
1. Delete Row (4)
2. 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.

26. 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

27. 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
1. 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
- -

28. 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
- -

29. 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
1. 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
- -

30. 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
1. Convert Col Letter To Number (G) = 7
2. 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

31. 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
1. Convert Col No to Letter (7) = G
2. 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

32. 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
1. 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.

33. 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
1. 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.

34. 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

35. 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.

36. 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

37. 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.
38. 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.
39. 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
40. 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.
41. 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.
42. 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.
43. 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.
44. 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
Note: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.
45. 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:
  • Value: Can copy and paste both numbers and values.
  • Number format: Can copy and paste both numbers and values.
  • Cell format: Copies along with cell format (font, colour).
  • Formula: Copies along with the formula specific to the cell.
46. 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.
47. 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.
48. 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.
49. 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.
50. 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.
Note: If the sheet exists, it returns True as output in the Result of Execution screen. Else, it returns False.
51. 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.
52. 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.
53. 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.
54. 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.
56. 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.
57. 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.
58. 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
- -

Automation Analytics and AI in a box

Contact Us

HfS Hot Vendor

Option3's Automation capabilities featured in HfS Research's Hot Vendors List for Q3, 2018

Access your copy here
  • Connect with us

    • Option3
    No. 1142, 6th Main,
    Sector 7, HSR Layout, Bangalore
    560102, India
    Phone: +91 997 292 4056

Copyright © 2018 Option3. All rights reserved.