◷ 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:
- Open the Excel sheet where you have saved the student scores.
- Calculate the Total.
- Calculate the average.
- Depending on the average determine pass/fail state.
- 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.
wb, ex, student, total, 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.