Student Record

◷ Reading Time: 7 minutes

Introduction

This tutorial will describe how to automate the process of reading and writing student records in an Excel sheet.

If we think of the process of reading student records and defining pass/fail state, the steps would be as follows:

  1. Open the Excel sheet where you have saved the student scores.
  2. Calculate the Total.
  3. Calculate the average.
  4. Depending on the average determine pass/fail state.
  5. Save the Excel sheet with a defined filename.

Video Description

Process Steps

In the following example, we will automate these five steps using Robotic Process Automation (RPA)- Excel Toolbox.

Create a new Generic-Flow file

Create a new file using the Generic Flow Template.

wbexstudenttotal, and average should be defined as Local parameters. See Variable Parameter in order to understand how to declare a variable parameter.

Open a new web browser

Add a Workbook Open tool to open the Excel sheet with student records.

Properties

  • Workbook Open
    • Path: pathCombine(pathCurrent(),’\\1.xlsx’)
    • Copy Result To: wb
  • Excel New
    • Copy Result To: ex
    • Visible: True

Read Records

Add a Cell Get tool to read data from the Excel sheet.

Properties

  • Workbook: wb
  • Sheet: records
  • Copy Results To: student
  • Cell Start: b2
  • Cell End: b5
  • Multiple Results: True

Calculate the total and average score

Add a Multi Expression tool to get total and average.

Properties

  • Expressions:

total=student|sum()average=student|avg()

Set Total

Add a Cell Set tool to set the total in the Excel sheet.

Properties

  • Workbook: wb
  • Sheet: records
  • Cell Start: b6
  • Cell End: b6
  • Values: total

Set Average

Same like total, set the average.

Properties

  • Workbook: wb
  • Sheet: records
  • Cell Start: b7
  • Cell End: b7
  • Values: average

Decide Pass/ Fail

Add a Decision tool to decide pass/fail status.

Set Pass/ Fail

Add a Cell Set tool to set pass/ fail status in the Excel sheet.

For “Pass” status,

Properties

  • Workbook: wb
  • Sheet: records
  • Cell Start: b9
  • Cell End: b9
  • Values: Pass

And for “Fail” status,

Properties

  • Workbook: wb
  • Sheet: records
  • Cell Start: b9
  • Cell End: b9
  • Values: Fail

Set Decision Transitions

Add a transition condition to the Pass direction of the decision and you can leave the other condition empty.

Properties

  • Condition: average>=50

Save Workbook

Add a Workbook Save tool to save the Excel sheet.

Properties

  • Path: pathCombine(pathCurrent(),’1_recorded.xlsx’)
  • Workbook: wb

Excel Close

Add an Excel Close tool to close the saved Excel sheet.

Properties

  • Excel: ex

Download the project

Use the attachment at the end of the page to download the sample project.

Updated on April 20, 2022

Article Attachments

Was this article helpful?

Related Articles