The Gradebook macro imports data from a University Testing Services text file into a Microsoft Excel spreadsheet. The grades are matched by student ID and placed in the column that is currently selected in the open spreadsheet. In addition, another spreadsheet is created which contains all of the records from the Testing Services file that could not be matched to ID’s in the main spreadsheet.

1. Open Excel
2. Open the spreadsheet you want to use the macro in
3. Click on the Tools menu and selection Macro then Macros… from the side menu
4. Type in "Gradebook" in the Macro Name: field
5. In the Macros In drop down list, choose the workbook you want to insert the macro into
6. Click the Create button
7. In the resulting window, delete the "Sub test()" and "End sub" lines so that the window is empty
8. Copy the macro code from this page and paste it into the empty window
9. Close the Visual Basic window, save the spreadsheet, and close Excel

The macro expects the students to be listed in a column with the grades for each student in rows. In order for the macro to function correctly, the following conditions must be met:

1. The student ID numbers must be listed in one of the first four columns (column A, B, C, or D) of the spreadsheet
2. The number of students in the testing services file must be no more than 800
3. The number of students listed in the grade spreadsheet must be no more than 800
4. The file to be imported must be plain text and formatted such that:

• there are 3 columns
• column 1 is the student’s ID number
• column 2 is the student’s name
• column 3 is the grade

To run the macro, click on the column in which the grades are to be placed. Next, click the Tools > Macro > Macros… menu items. Highlight the macro and click Run. The macro will request the path and filename of the Testing Services file. Type the path and filename in and click OK. A dialog box will then appear stating that file has been located and the macro is ready to proceed with the import operation. Click OK. 

The import operation make take up to a few minutes. Once the macro has completed the import, a dialog box will appear stating that operation is done. Click OK to close the macro and return to the spreadsheet. The spreadsheet will now contain the grades for each student in the designated columns and a second spreadsheet will be present that contains any unmatched records.