Practice Problem 2

Objective

The objective of this practice problem is for the user to demonstrate an understanding of the Excel commands discussed in the previous section by creating an automation that utilizes said commands. The commands covered in the previous section were the Create Excel Application, Close Excel Application, Get Last Row Index, Write Row, Write Cell, Get Cell, Activate Sheet, Append Row, Append Cell, Delete Row, Delete Range, Save Workbook, and Save Workbook As commands. In the future, these commands may be implemented into other practice problems.

Scenario

A research assistant of ABC institute is asked by the Dean of the institute to update an Excel workbook containing grades of students. This task can be attained by opening the Excel sheet, loading more information into it, overwriting some existing information since it is incorrect, deleting some information which are not applicable or required, saving the workbook and closing the Excel Workbook.

Prerequisites

The user must use a pre-existing Excel workbook file (.xlsx) before starting to work on the solution. The Excel file must also be closed when the process begins. Below is a screenshot of the Excel file that was created for this problem, feel free to recreate it or create a unique file. Feel free to delete this file when finished with the exercise, although this will mean that the automation cannot be run again.

Practice Problem Requirements

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

Creates an Excel Instance with a unique name (User may decide the name for the instance name variable)
Opens an Excel workbook (existing file) that is visible while the automation is running.
Appends a new row in the workbook (all variables that must be added should be provided as a list of values.)
Attains Last Row Index (a column needs to be specified to show from which point the last row index is calculated)
Gets a value of a cell and stores it in a variable
Writes the value of the above-mentioned variable into another cell (location of the cell that needs to be modified must be specified)
Overwrites a row (All values that needs to be overwritten, must be separated by commas. Location of the row that needs to be overwritten must be specified)
Deletes a row (Row number must be specified. User may decide if the cells from the next row should be shifted up or not)
Deletes a range of cells (Range numbers must be specified. User may decide if the cells from the next row should be shifted up or not)
Saves a copy of the file with a different name in a separate folder (the user can decide which folder the file is saved to if it is not the original folder)
Closes the Excel Instance once the other commands have been completed.

Problem Difficulty: Medium
Solution will be provided on the next page with a brief explanation.

Solution

The code for the solution has been provided below.

As a quick summary:

Step 1: The Create Excel Application command must be called first because it tells the automation that it needs to work with an Excel file instance. The Excel Instance Name in the solution code was “vExcel” and the path to the Excel sheet called “Marksheet” is mentioned correctly. The Visibility and Close Instances fields can both be set to yes.

Step 2: The Append Row command will allow a row to be added to the end of this workbook. In this practice problem, a new student’s data is appended.

Step 3: The Get Last Row Index command is used to calculate the index of the last row. A column must be specified so that it tells the automation from where to start counting. The value attained after calculating the index is stored in a variable. In this practice problem, this variable is called “vLastRowIndex”.

Step 4: The Get Cell command is used to get the value of a cell. This can be done only if the cell location is specified correctly. The value in this cell is later saved to a variable. In this practice problem, this variable is called “vCell”.

Step 5: The Write Cell command is used to write/overwrite the value that is attained using the Get Cell command. The value attained using Get cell command is saved in a variable called “vCell”. This value is written into the cell location that is specified in the Write Cell command. In this practice problem, this cell is overwritten because the data mentioned in column “State” for this student “Matthias” was incorrect and required rectification.

Step 6: The Write Row command will allow a row to be overwritten. The row that needs to be modified must be specified correctly and the values that need to be entered must be provided as a list. In this practice problem, a student’s grades were entered incorrectly and thus, the student was erroneously marked as “Failed”. The dean of the institute demanded immediate rectification of this error.

Step 7: The Delete Row command will allow an entire row to be deleted. The row number must be specified. In this practice problem, the student left the institute and his data was no longer needed. Thus, the dean asked for the deletion of his records.

Step 8: The Delete Range command will allow a range of cells to be deleted. The Range numbers must be specified correctly. In this practice problem, the dean of the institute requested the phone number and house be deleted from each students’ record as this data was not applicable/needed.

Step 9: The Save Workbook As command will allow the specified Excel Instance to be saved with whatever the user designated as the name of the file in the specified file location. In this practice problem, this Excel Sheet is renamed as UpdatedMarksheet1 and is saved in the Desktop.

Step 10: The Close Excel Application command will allow the specified Excel Instance to be closed as it is no longer needed. The Save on Close field is set to No because the file has already been saved.

Lets stay in touch!