Example for sharing an Excel session between bots

Build two bots to update the employee salaries in a worksheet. In this example, one bot opens an Excel worksheet and passes the session name as a variable to the second bot, which iterates through the worksheet, calculates each employee's new salary, and updates the value in the worksheet.

Prerequisites

Create a worksheet with the following data:
Employee ID Start date Salary
200 1/12/2020 50000
233 3/15/2018 75000
500 6/6/2020 60000
555 7/9/2019 65000

Procedure

Build the child to iterate through the table of employees and update each salary to reflect the raise.

  1. Open a new bot.
    1. From Automation Anywhere web interface, on the left panel, click Automation.
      A list of available bots and forms is displayed.
    2. Click Create new > Task Bot.
    3. Enter the bot name RaiseSalaries.
    4. Enter the folder location \Bots\TaskBotExample.
      To change where your bot is stored, click Choose and follow the prompts.
    5. Click Create and Edit.
  2. Create the following variables:
    • sessionVariable: Set the Type - Session, Session type- MS Excel session; and select Use as input.
    • sCellAddress: Set the Type - String; and set the Default value - C2.
    • sSalary: Set the Type - String
    • nSalary: Set the Type - Number
  3. Use a Loop action to iterate through all the rows in the worksheet.
    1. Double-click or drag the Loop action.
    2. In the Loop Type > Iterator, click the Loop drop-down menu.
    3. In the Excel advanced option, select For each row in worksheet iterator.
    4. In the Read option field, select Read cell value option.
    5. In the Session name tab, insert $sessionVariable$.
    6. In the Assign the current value to this variable > Record tab, create a record variable.
  4. Retrieve the employee's salary.
    1. Drag into the Loop container the Excel Advanced > Get single cell action.
    2. In the Cell option field, select Specific cell option and insert $sCellAddress$ in the Cell address field.
    3. In the Read option field, select Read cell value option.
    4. In the Session name tab, insert $sessionVariable$.
    5. Click the Store cell contents to drop-down menu and select $sSalary$.
  5. Convert the salary value to a number data type.
    1. Drag into the Loop container the String > To number action.
    2. In the Enter the string field, insert $sSalary$.
    3. Click the Assign the output to variable drop-down menu and select $nSalary$.
  6. Calculate the new salary to be five percent greater than the current salary.
    1. Drag into the Loop container the Number > Assign action.
    2. In the Select source variable field, enter $nSalary$*1.05.
    3. Click the Select the destination number variable drop-down menu and select $nSalary$.
  7. Update the salary in the worksheet.
    1. Drag into the Loop container the Excel Advanced > Set cell action.
    2. In the Cell option field, select Specific cell option and insert $sCellAddress$ in the Cell address field.
    3. In the Cell value field enter $nSalary.Number:toString$.
      Note: You must convert the value to a string data type before the bot can enter it into the worksheet. Use type casting as an alternative to the Number > To string action.
      lt;YourStringVariable>.String:toNumber$ to convert the value of a string variable to a number variable to perform mathematical operations.">Type conversion
  • In the Session name tab, insert $sessionVariable$.
  • Move the cursor down by a single cell and assign that cell address to sCellAddress.
    1. Drag into the Loop container the Excel Advanced > Go to cell action.
    2. Select the Active cell and from the drop-down menu, select One cell below.
    3. In the Session name tab, insert $sessionVariable$.
  • Insert the Loop container.
    1. Drag the Excel Advanced > Get cell address action.
    2. In the Cell option field, select Active cell option .
    3. In the Session name tab, insert $sessionVariable$.
    4. Click the In the Session name tab, insert $sessionVariable$ drop-down menu and select $sCellAddress$.
  • Click Save.

    • Build the parent bot.

    1. Open a new bot.
      1. Click Create a bot.
      2. Enter the bot name ExcelParentBot.
      3. Enter the folder location \Bots\TaskBotExample.
        To change where your bot is stored, click Choose and follow the prompts.
      4. Click Create and Edit.
    2. Create the following variable:
      sessionVariable: session type; MS Excel subtype
    3. Double-click or drag the Excel Advanced > Open action.
    4. Within the File > Desktop file tab, click Browse to upload a file:
    5. Select the Sheet contains header check box.
    6. In the Create Excel session field, click Global session tab and use the Insert a value icon to select sessionVariable.
    7. Select the child bot that you have created.
      1. Double-click or drag the Task Bot > Run action.
      2. In the Control Room file tab, click Choose to select the RaiseSalaries bot.
      3. In the Input values section, select the Global session option and insert $sessionVariable$.
    8. Click Save and Run.
      The Excel worksheet opens and the value of each cell in the third column increases by five percent.