Expressions - Excel

01. Open Excel

Use this excel command to open a specific file. This function supports .xls, .xlsx, .xlsm and .csv format This command must be mandatorily used at the start of the node.

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

Use this excel command to open a password protected excel file.

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

Use this excel command to specify excel sheet name to be automated. This function must be used after the ‘Open Excel/ Open Protected Excel ’ command.

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

Use this excel command to specify excel sheet index to be automated. 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 of 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 values from the selected cells of the active sheet from the opened excel file.

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.

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