Robotic Process Automation (RPA)

 View Only
Expand all | Collapse all

RPA studio - Excel, when does this sum up all the values in monthly sheets

  • 1.  RPA studio - Excel, when does this sum up all the values in monthly sheets

    Posted Thu May 26, 2022 02:37 AM
      |   view attached
    https://learn.ibm.com/mod/page/view.php?id=202775&forceview=1

    I'm checking this IBM Training - Learning Paths and Collections


    ------------------------------
    HYANG GI JEONG
    ------------------------------

    Attachment(s)

    xlsx
    Example company income and expenses.xlsx   22 KB 1 version


  • 2.  RE: RPA studio - Excel, when does this sum up all the values in monthly sheets

    Posted Fri May 27, 2022 01:38 AM
      |   view attached
    It is in https://learn.ibm.com/course/view.php?id=10305

    I changed its extension to zip. so please rename it as Activity solution.wal


    ?defVar --name excelExampleCompanyInstance --type Excel
    defVar --name yearWorksheetExist --type Boolean
    defVar --name currentRow --type Numeric
    defVar --name currentValueInText --type String
    defVar --name currentValueInNumber --type Numeric
    defVar --name yearValue --type Numeric
    defVar --name collectionOfRows --type List --innertype Numeric --value "[2,3,4,8,9,10,11,12]"
    defVar --name yearTotal --type String
    defVar --name collectionOfMonths --type List --innertype String --value "[January,February,March,April,May,June,July,August,September,October,November,December]"
    defVar --name currentMonth --type String
    defVar --name exampleCompanyExcelFilePath --type String
    //Description:
    // This script is a solution example for the proposed activity in the course.
    // This script automates the creation of the income and expenses table in the year.
    //To use this script:
    // It's required the Excel file example (Example company income and expenses) provided in the activity.
    // The excel file cannot be in use while the bot is running.
    //About this script:
    // Created in version 21.0.1 of IBM Robotic Process Automation.
    inputBox --title "Select the Excel file" --prompt "Select the Example Company excel file" --value "C:\\excelActitity\\Example company income and expenses.xlsx" --comment "Is used this command to get the Excel file path from the user." exampleCompanyExcelFilePath=value
    excelOpen --file "${exampleCompanyExcelFilePath}" --savechanges excelExampleCompanyInstance=value
    goSub --label CreateYearWorksheet
    goSub --label SetValuesInExcel
    goSub --label CalculateValuesInExcel
    excelGet --file ${excelExampleCompanyInstance} --sheet Year --row 15 --column 2 --comment "This command gets the year earnings after performing the annual income, expenses, and earnings calculations." yearTotal=value
    excelClose --file ${excelExampleCompanyInstance}
    messageBox --title Congratulations --text "Your annual earnings are $ ${yearTotal}!" --icon "Information" --buttons "OK" --defaultbutton "FirstButton"
    beginSub --name CreateYearWorksheet
    //This routine verifies if the "Year" worksheet is already created. If "Year" it's not created this routine creates it.
    excelWorksheetExists --file ${excelExampleCompanyInstance} --name Year yearWorksheetExist=value
    if --left "${yearWorksheetExist}" --operator "Is_True" --negate
    excelCloneWorksheet --file ${excelExampleCompanyInstance} --worksheet December --newworksheet Year
    endIf
    excelSet --value "Year total" --file ${excelExampleCompanyInstance} --sheet Year --row 15 --column 1
    endSub
    beginSub --name CalculateValuesInExcel
    //This routine inserts the formulas for calculating annual income, expenses, and earnings.
    excelSet --value "=b2+b3+b4" --isformula --file ${excelExampleCompanyInstance} --sheet Year --row 5 --column 2
    excelSet --value "=b8+b9+b10+b11+b12" --isformula --file ${excelExampleCompanyInstance} --sheet Year --row 13 --column 2
    excelSet --value "=B5-B13" --isformula --file ${excelExampleCompanyInstance} --sheet Year --row 15 --column 2
    endSub
    beginSub --name SetValuesInExcel
    //This routine fills in the income and expense values of the ?쏽ear??worksheet based on the values obtained in the months.
    foreach --collection "${collectionOfRows}" --variable "${currentRow}"
    setVar --name "${yearValue}" --value 0
    foreach --collection "${collectionOfMonths}" --variable "${currentMonth}"
    excelGet --file ${excelExampleCompanyInstance} --sheet "${currentMonth}" --row ${currentRow} --column 2 currentValueInText=value
    convertStringToNumber --text "${currentValueInText}" --allowleadingsign --allowdecimalpoint --allowthousands --culture "en-US" currentValueInNumber=value
    evaluate --expression "${yearValue}+${currentValueInNumber}" yearValue=value
    endFor
    excelSet --value "${yearValue}" --file ${excelExampleCompanyInstance} --sheet Year --row ${currentRow} --column 2
    endFor
    endSub* 20.12.4.0



    ------------------------------
    HYANG GI JEONG
    ------------------------------

    Attachment(s)

    zip
    Activity solution.zip   3 KB 1 version
    Original Message


  • 3.  RE: RPA studio - Excel, when does this sum up all the values in monthly sheets

    Posted Tue May 31, 2022 09:56 AM
    The SetValuesInExcel routine loops through all rows in the spreadsheet (collectionOfRows), then it loops through the months (collectionOfMonths), then uses the excelGet command to get the value of a cell - using the current row (currentRow) and the second column). Then it sums the year with the cell value retrieved from the excelGet using the evaluate command (${yearValue}+${currentValueInNumber}), and finally updates the cell value using the excelSet command.

    ------------------------------
    Joba Diniz
    Product Manager
    IBM
    ------------------------------

    Original Message