Excel node is used to access an excel sheet and perform different actions (like adding, updating and editing cells) in excel sheets using Jiffy. Any excel sheet can be accessed and automated directly using Excel node.

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

Setting up Excel Configuration

1. Double-click on the Excel node.
2. Select any existing configuration if it matches the criteria of the user or create a new configuration. The user can also edit or copy the existing configuration by clicking on the Edit and Copy icons displayed against each configuration, respectively.
3. Click on the New Configuration radio button to create a new configuration if required.

Add the following details:

• Configuration Name: Type a unique configuration name.
• Clusters: Select the required cluster from this drop-down.

Properties of Excel Node

1. Double-click on the Excel node and then click on the Properties tab.
2. Provide the following details:
   1. Name: Name of the node. A default name is displayed in this field, which the user can edit according to the task and the intent of using the node.
   2. Description: A short note on the purpose of the node.
   3. Mark run Failure on Node Fail: When the Mark run Failure on Node Fail field is ON, if the node execution fails then the complete task execution is marked as fail.
   4. Continue on Failure: When the Continue on Failure field is ON, even if the node fails, the execution will continue to the next node. When the Continue on Failure field is OFF, if the node fails, the execution will not continue to the next node.
   5. Run Mode: The user can select any one of the following options from the Run Mode drop-down:
      1. Run if locked: The task runs even if the machine is locked.
      2. Unlock and run: Unlocks the machine and runs the task.
   6. Lock After Run: Locks the machine after the node execution.

Excel - Actions

The default Excel Cmd option in the list box (1). This list box (1) contains the following options:

• Excel Cmd: To use pre-defined Jiffy Excel commands. Refre to the Excel commands for more details.
• Run Macro: To run the Excel macros (link to be given to the below section
• Custom Excel Cmd: To use the Excel commands created by the user.
• Set Variable: To store the output data of the Excel to the variable specified in this list box. The value to be set can either be a new variable or an existing variable.
• Pre-defined Function: Commonly used functions that are designed by JIFFY.ai are displayed in this drop-down.
• User Defined Function: To use the functions defined by the user.

Using the Excel Cmd functionality, the user can perform various actions on the Excel sheet by selecting the required option from the list-box (3). For more details, refer variables.

• Copy: Single line or multiple lines of actions can be copied to the clipboard by selecting the line/lines of actions by checking the checkbox/checkboxes of the desired line/s of actions and then clicking the Copy button on the toolbar.  The lines need to be selected in a sequential order, they cannot be select randomly.
• Comment: The line/lines of actions that are not in use or not required can be commented using this option. To comment a line of action, do the following:
  Select the desired line of action by checking the checkbox provided against each line. Multiple lines of actions can be selected by clicking on the checkboxes of the desired lines of actions. Click on the Comment button in the toolbar.

    The line/s of action is/are commented then the Comment button will be disabled and the Uncomment button is enabled only for that selected line/s. Upon hovering the disabled Comment button, a tooltip appears The line is commented already.

• Uncomment: The line/lines of actions that are commented can be uncommented. To uncomment a line of action, do the following:
  Select the desired line of action by checking the checkbox provided against each line. Multiple lines of actions can be selected by clicking on the checkboxes of the desired lines of actions. Click on the Uncomment button in the toolbar.

    Once the line/s of action is/are uncommented then the Uncomment button will be disabled, and the Comment button is enabled only for that selected line/s. Upon hovering the disabled Uncomment button, a tooltip appears The Lines are not commented yet

• Debug: Explained Debug section.
• Select all/Deselect all: To select or deselect all of the lines of action at once.
  

The user can comment/uncomment steps added in the Actions tab by selecting the check box against the required step and then click on the Comment or Uncomment options. Commenting the steps results in excluding the selected step in the node execution. The user can also select all the steps at once by clicking on the Select all option and then perform various actions like copy/comment/uncomment.

For more information on the options avaiable in the Actions tab

For information on debug functionality, refer to the Debug section.

Excel Macros

If the user wants to automate the repeated tasks in Microsoft Excel, the user can record a macro to automate those tasks. A macro is an action or a set of actions that the user can run as many times as required. When a macro is created, the mouse clicks and keystrokes are recorded.

In order to use macros, the excel sheet used must be macro enabled with file extension .xlsm

Excel must be configured to accept VBA macros execution. Select the below highlighted options in the File -> Option -> Trust Center -> Macro Settings screen.

Add Excel Macro

1. Double -click on the Excel node.
2. Click on the ≡ triple bar icon (1).
3. Click on the + icon (2).
4. Specify the following details:
   1. Name: Type the name of the Macro.
   2. Description: A short note on the purpose of the Macro.
   3. Module Name: Provide a specific module name.
   4. Function Name: Type the function name that is present in the Macro script. Refer the screenshot at the end of the screen for more details.
5. Click on the Edit icon (3).
6. Type or Copy the code of the macro from the excel sheet (Excel file -> Developer -> Macros -> Edit button) and paste the code to the Function text box.

   Make sure that the Function Name is same as the code following the “Sub” code line as highlighted below. If it is not same, edit the Function Name.

   

Refer to Excel-Web Automation sample task to know how to use Excel node in a task.