- 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.
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.
