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 a 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: session type; MS Excel subtype; use as input
    • sCellAddress: string type; default value of C2
    • sSalary: string type
    • nSalary: number type
  3. Use a Loop action to iterate through all the rows in the worksheet.
    1. Double-click or drag the Loop action.
    2. Select the For each row in worksheet iterator.
    3. Select the Shared session tab and insert $sessionVariable$.
    4. Select the Read cell value option.
    5. In the Assign the current value to this variable field, create a record variable.
  4. Retrieve the employee's salary.
    1. Drag into the Loop container the Excel Advanced > Get single cell action.
    2. Select the Shared session tab and insert $sessionVariable$.
    3. Select the Specific cell option and insert $sCellAddress$.
    4. Select the Read cell value option.
    5. In the Store cell contents to field, insert $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. In the Assign the output to variable field, insert $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. In the Select destination variable field, insert $nSalary$.
  7. Update the salary in the worksheet.
    1. Drag into the Loop container the Excel Advanced > Set cell action.
    2. Select the Shared session tab and insert $sessionVariable$.
    3. Select the Specific cell option and insert $sCellAddress$.
    4. 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
  • 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 Shared session tab and insert $sessionVariable$.
    3. Select the Active cell and One cell below options.
    4. Drag into the Loop container the Excel Advanced > Get cell address action.
    5. Select the Shared session tab and insert $sessionVariable$.
    6. Select the Active cell option.
    7. In the Save active cell address field, insert $sCellAddress$.
  • Click Save and Close.
  • 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.