Practice Problem 2

Objective

The objective of this practice problem is for the user to demonstrate an understanding of the If Statement commands discussed in the previous section by creating an automation that utilizes said commands. The commands covered in the previous section were the Multi While, End Multi While, Continue, and the Break commands. Only the Multi While, End Multi While, and Continue commands from the previous section will be used in this problem. In the future, these commands may be implemented into other practice problems.

Scenario

The ABC school district needs to create an automated grading system that will read an Excel file containing all of the students’ grades and provide a final letter grade to all students based on the score table and the overall score. The student’s Honors status also needs to be determined based on their overall score (95 or above) and if they finished the class. Also, if a student’s grade is 85 or above, the teacher will provide them with a free slice of pizza, so this must be marked on the Grade Sheet as well. Once the letter grade has been calculated, the Honors status updated, and their free pizza status has been written, the Excel sheet is emailed to the principal for final review with message that states that the grade sheet is completed and states how many earned a free slice of pizza.

Prerequisites

The only prerequisite for this practice problem is an Excel file that is formatted in a manner as seen below. The Name, Subject, and Class Average can be set to whatever the user wants, but the criteria in the Score Table must remain the same. The Finished Class field has to be set to either Yes or No. Place the file in the project folder next to the Main .JSON file.

Practice Problem Requirements

The user must create an automation that performs the following tasks:

Opens the prerequisite file.
Extracts the Finished Class and Overall Score values as well as the number of entries (should be dynamic) Hint: Use Get Last Row Index
Uses a Multi While command to loop through all of the entries in the Excel file while a counter is less than or equal to the number entries and the number of people who got free pizza is less than or equal to the number of entries.
Uses multiple Multi If commands to calculate the Grade value using the Score Table.
In the If statements
- Writes the letter grade into the Grade field.
- Writes Yes in the Graduate w/ Honors field based on if the Overall Score is 95 or over and the Finished Class value is Yes. Otherwise, for all other Grades, the Graduate w/ Honors field should be set to No.
- Writes Yes in the Get Celebratory Pizza if the student’s grade is 85 or above.
- Moves on to the next loop once the items have been written down.
Saves the file and emails it to an address determined by the user. The message must contain the number of people who earned free pizza.

1st Note: The user should be able to edit the Overall Score and Finished Class values and still have the automation calculate the necessary values.

2nd Note: Writing the name of a variable inside a text field will cause the value of the variable to display when the automation is run. This also works with variables as well.

Example 1: vPizzaCount + “ students earned pizza this semester!”
This will display as “7 students earned pizza this semester!” if vPizzaCount equals 7

Example 2: In a command that requires an Excel cell location, writing the column letter followed by a variable will append the variable’s value to the column letter.
Cell “C” + vCounter.ToString() will be Cell C7 if vCounter equals 7.

Problem Difficulty: Medium

Solution

The code for the solution has been provided below.

As a quick summary:

Step 1: Open the prerequisite file using the Create Excel Application command. Also create variables to act as the iterative variable and the number of students that earned celebratory pizza.

Step 2: Use the Get Last Row Index command to get the number of entries and store the number in a variable.

Step 3: Use a Multi While command to loop while the iterative variable and the pizza counter are less than the number of entries.

Step 4: Within the loop, extract the Overall Score and Finished Class values and then use Multi If commands to compare the values in the Score Table to the Overall Score.

Step 5: Within each of the Multi If, write the appropriate Grade, Graduate w/ Honors, and Pizza values to the Excel sheet using the Write Cell command. Also, increase the counter by 1 and move to the next loop.

Step 6: Within the final Multi If command, write the appropriate Grade value and check to see if the Finished Class value is Yes or No. If it equals Yes, write Yes in the Graduate w/ Honors field. If it equals No, write No.

Step 7: Close the file and email it to another user using the Send Outlook Email command. Ensure that the message displays the correct number of people who earned pizza.

Lets stay in touch!