Excel node is used to access an Excel spreadsheet and perform different actions that enable you to automate many of the repetitive tasks when working with Microsoft Excel spreadsheets.

You can automate tasks related to the workbook, worksheet, rows, columns, and cell operations.

Using an Excel node, you can get the cell value from an Excel spreadsheet, manipulate it, assign it to a variable and map it to the succeeding node for further automation steps.

To use an excel node, ensure that Microsoft Excel (Licensed) application is installed in your machine.

Configurations of Excel node

1. Double-click the Excel node.

2. Enter the Configuration Name and Cluster in Configurations tab.

Properties of Excel Node

1.  Navigate to Properties tab and enter Name, Description, and Run Mode.
2. Enable Mark run Failure on Node Fail, Continue on Failure, and Lock After Run options as required.

Actions in Excel Node

In Actions tab, you can define actions to be performed in Excel node.

Double-click the Excel node and navigate to Actions tab. Select one of the following options from the first drop-down:

• Excel Cmd: To select required Predefined Excel Functions.
  
• Custom Excel Cmd: Custom Excel Functions are created for specific needs if not met by the Predefined Excel functions. All the User Defined Functions created for the HyperApp with Type selected as Excel are listed in drop-down.
• Call Function: To select non-Excel Functions from the second drop-down.
  To know more about how to use Functions, click here.
• Set Variable: To set the value for Variables to use in further steps. The value set can either be a constant or another variable.
  

You can copy, comment, uncomment, debug, select all, or deselect all actions using the Toolbar options.

Excel Macros

You can automate repetitive tasks in Microsoft Excel using Excel Macros. A Macro is an action or a set of actions that you can run as many times as required. When a Macro is created, the mouse clicks and keystrokes are recorded.

You can also execute Macros using the Run Macro pre-defined function.

Excel Prerequisites

Excel must be configured to accept VBA macros execution.

Navigate to File > Option > Trust Center > Macro Settings page and enable the following highlighted options.

Add Excel Macro

1. Click the icon on Design Studio Page.

2. Select the Excel Macros option to display Excel Macros tab.

3. Click the icon to add a new Excel Macro.

4. Specify the following details in the Add New Excel Macro window.

   • Name: Type name of the Macro.
   • Description: Short note describing purpose of the Macro.
   • Module Name: Provide a specific module name.
   • Function Name: Type the function name that is present in Macro script.

5. Click the Save button.

Edit Excel Macro

All Excel Macros created are displayed in Excel Macros tab.

1. Click the icon against newly created Excel Macro.
2. Write the Excel Macro Script in the Function text box.
3. Click the Save button.

The Function Name must be the same as the code following the Sub line.

Run Macro

All Macros created in that node gets displayed in the drop-down. Select the Macro based on your requirement.

Variables

All Input/Local variables created for the node are displayed under Variables tab.

Table Definitions

All Default and Custom DataTables created in the Task are displayed under Table Definitions tab.

Result of Execution in Excel Node

After executing task, Result of Execution window is displayed with Input, Excel Output, Run Info, Raw Output, and Step Data tabs.

• Input: The input variables that are mapped to the Excel node are displayed.
  
• Excel Output: The value of the output variable is displayed.
  
• Run Info: The Run Details, Configuration Details, and Machine Details are displayed.
  
  • Run Details: Run Details include Run ID, Sequence Number, Iteration ID, and Total Node Execution Time (in seconds).
  • Configuration Details: Configuration Details include Configuration Name, App, Clusters, and Config Level.
  • Machine Details: Machine Details include ID, Name, Host, Port, Iteration Start Time, Iteration End Time, and Iteration Time (in seconds).
• Raw Output: Execution status and values of output variables in the current node that can be mapped to succeeding nodes are displayed.
• Step Data: The details and status of each Line of Action inside the node are displayed.