Troy University, Troy Campus à College of Arts & Sciences à Chemistry à King à GetGrades Software


The GetGrades Software Package:

Downloading & Installing


        The GetGrades package consists of three parts:  An Excel add-in and two cgi scripts, written in Perl.  One script displays the password pages and the “student’s scores” page; the other displays the course “scores distribution” page.  The Excel add-in has not been tried on a Macintosh.  The three components of GetGrades may be downloaded here: 

Post Grades.xla           the Excel Add-in

getgrades.cgi                the main Perl cgi script

hist.cgi                            the Perl cgi script that displays the “scores distribution” page


Installation involves the following steps:

·        Set up a cgi directory and a “secure” directory for data files

·        Install the Excel add-in and set up the method of transferring files

·        Add filenames and course identifiers to the cgi files

·        Add a form to your web page to run the cgi files

Setup Directories

        The first step in the installation is to ask your network administrator to allow you to run cgi files on your web server, and to find out what directory the cgi files need to be placed in.  You will also need direct access to a secure directory to store your data files in.  Because these files contain scores for the entire class, it is your responsibility to see that the score information remains confidential.  Therefore, the contents of your directory must not be viewable by a web browser.  To test the access to a file, enter its address in your browser.  You should get error message 403 (“Forbidden:  you don’t have permission to access such & such a file.”), or possibly 404 (File or directory not found).  If you enter just the directory’s address, you should get the same error messages.  You may have to work with the network administrator to set up this secure directory.  If your account is on a Unix machine and you are given a cgi directory, such as cgi-bin, in your account then you may find that a subdirectory of the cgi-bin directory is secure.

Install the Add-in

        You are now ready to install the Excel add-in.  Move “Post Grades.xla” to the AddIns directory.  The default path to this folder is C:\Windows\Application Data\Microsoft\AddIns, or, if your system uses user profiles, C:\Windows\Profiles\UserName\Application Data\Microsoft\AddIns.  Once the file is in the correct directory, go to the Add-Ins…dialog box (on theToolsmenu).  Put a check mark next to the entry “Post Grades”.  Click OK and “Post Grades” will install.  You can tell that the installation succeeded by the appearance of Post Grades on the Toolsmenu.


        The “Post Grades” add-in will send the output files—the student’s score data file and the scores distribution data file—to a location you specify, such as on your web server.  To specify where to save the output files, select Post Grades Setup on the Tools menu.  The screen will look like this:

If the “Place output files in Same directory as the workbook” option is selected, the output files will be placed in the same directory that contains the workbook being processed.  You will have to move them to a web server manually.


If the “Output file Location:” option is selected, an output location must be specified.  This location may be:

·        A directory on your computer

·        A remote FTP site, as shown in the figure

·        The directory of a remote computer, if you are connected to a network.  Double backslashes are used to indicate the name of a computer, as in \\WILL B. DOOD\STORAGE, where STORAGE is a directory on the computer named WILL B. DOOD.

The browse button provides a convenient way of selecting an output location.  It brings up the Microsoft Office “Save As” dialog box.  (To use this dialog box, just go to the directory where you want information stored.  It is not necessary to actually select a file in that directory.)  Notice the options in the “Save in:” drop-down menu.  A remote computer can be selected using either “Network Neighborhood”, orFTP Locations”.


For FTP sites, Microsoft Office only remembers passwords for the current session, so the next time Excel starts, “Post Grades Setup” would have to be run again to reenter the password.  To get around this, you can let “Post Grades” handle FTP.  Put a check in the checkbox “Location is an FTP site; let Post Grades Handle it”.  The browse button can still be used to select the FTP site.  Enter your user ID and password for the FTP site on the “Post Grades Setup” form.  This data will be remembered by the program, even if the computer is turned off.


Here is how Post Grades handles FTP.  The following files are created in the same directory as the Excel workbook:  the output files, a file of FTP commands, and a batch file, postgrades.bat.  An FTP program, ftp.exe, is in the “Windows” directory.  The batch file sends the file of FTP commands to ftp.exe, which sends the output files to the FTP site.  The batch file then deletes the output files and the file of FTP commands (which contained your password).  The batch file is not deleted.  It is overwritten every time Post Grades is run, so you may delete it if you wish.


        Ah, but does Post Grades really work?  At this point, you may want to download an example spreadsheet (right click on that link and choose “save target as…”) to test the add-in.  Open that workbook, run “Post Grades Setup”, select the “Post Grades” sheet, select the cell under worksheet name, and run the add-in.  Hopefully, the output files will be transferred to the desired directory. 

Install the cgi Files

        The data files contain confidential information, so neither the names nor locations of these files will be given on your web pages.  Instead, your web page sends an arbitrary course id, such as “GenChem1_section1” to getgrades.cgiGetgrades.cgi looks up the course id and finds out which file name is associated with it.  When getgrades.cgi is done with its processing, it sends the course id to hist.cgi, which looks up the name of the file it should use.  The next step, then, is to create the look-up table in getgrades.cgi.


        Open getgrades.cgi in notepad (any word processor can be used, but it must be saved as a text file).  Near the top you will see the following lines:




#                    course id           filename

my (%course_file) = qw(

                KingGenChemI_sec1     info/GenChemOne_1.txt

                KingGenChemI_sec2     info/GenChemOne_2.txt

                KingGenChemI_sec3     info/GenChemOne_3.txt

                KingElemChem          info/ElementaryChem.txt

                KingGenChemII         info/GenChemTwo.txt

                KingAdvInorChem       info/AdvInorgChem.txt

                KingInorChemLab       info/AdvInorChemLab.txt

                KingOrgILab_sec1      info/OrgChemOneLab_1.txt

                KingOrgILab_sec2      info/OrgChemOneLab_2.txt



As you can see, I have nine course id’s listed, and a filename associated with each one.  Feel free to add lines or delete lines from the list.  Just don’t delete that “);” marking the end of the list.  The filename gives the location of the file relative to the cgi directory.  The examples shown would be in a subdirectory called “info”.  As mentioned above, this must be a “secure” directory.  Remember that four pieces of information are sent to “Post Grades”?  The filenames above are the possible values for the third piece. 


        One more item needs to be personalized in this file.  Scroll down a little over half-way in the file and you will find the following:


print em("Note: &nbsp"). $note, br() if ($note);  


# !!!!!!!!!!!!!!!!!!!!! REPLACE THE LINK BELOW WITH YOUR LINK !!!!!!!!!!!!!!!!!!!

unless ($skip_hist) {

   print startform('post', ''),

            p( "View the distribution of scores on all exams: &nbsp",


See the link  Replace that with the link to hist.cgi on your computer.  (Our server can provide a secure http connection, which is why I used https instead of http.)  Now save the changes you have made to getgrades.cgi.


        Open up hist.cgi and you will see the following lines:




#                    course id           filename

my (%course_file) = qw(

                KingGenChemI_sec1     info/GenChemOne_1Hist.txt

                KingGenChemI_sec2     info/GenChemOne_2Hist.txt

                KingGenChemI_sec3     info/GenChemOne_3Hist.txt

                KingElemChem          info/ElementaryChemHist.txt

                KingGenChemII         info/GenChemTwoHist.txt

                KingAdvInorChem       info/AdvInorgChemHist.txt

                KingInorChemLab       info/AdvInorgChemLabHist.txt

                KingOrgILab_sec1      info/OrgChemOneLab_1Hist.txt

                KingOrgILab_sec2      info/OrgChemOneLab_2Hist.txt



The course id’s must be identical to the ones in getgrades.cgi .  Change the filenames to match whatever you want to use for the score distribution files.  These will be the possible choices for the fourth forth piece of information sent to “Post Grades”.  Save your changes and close these two files.


        Send both getgrades.cgi and hist.cgi to the directory on your web server that runs cgi scripts.  Be certain to transfer these as text files, not binary files.  If you send them to a Unix server, open the file from the Unix machine and check that it doesn’t contain characters that look like black rectangles.  If it does, the file was probably transferred as a binary file, and probably won’t run.  


Finally, if you are using a Unix machine, the file attributes will probably need to be changed to make the files readable and executable by the world.  First, connect to your site by ftp.  If you are using Internet Explorer, you would enter something like the following in the address bar:  “”.  After being prompted for a username and password, select a cgi file, click on it with your secondary mouse button (i.e., right click), and choose properties from the pop-up menu.  Both “Read” and “Executable” should be checked for all users.

Add a Form to Your Web Page

        Next, your web page needs to be set up.  This section describes how to set the page up if you have just one section of a course.  The last paragraph describes how to set up a page to handle multiple sections. 


A web page uses a “form” to call getgrades.cgi.  If you use Word, the easiest way to add the form is to copy the following section into a Word document.


Copy from here


All of your grades may be viewed.  Click

the button to go to the password page.


To here.


To see what you’ve got, click the show/hide button, , on Word’s standard toolbar.  A button and two hidden text boxes are shown.  You will need the web tools toolbar, so click on a menu bar or toolbar with your secondary mouse button and select Web Tools.  Now select design mode (click on ; see figure below).  Click on the Get Grades button with your secondary mouse button and choose properties.  This will display the button’s properties.  The entry for “Action” is  Change this to the address of getgrades.cgi on your machine.  Notice that I used https again instead of http, since a secure connection is available on our computer.  Also, the next entry is caption; you can customize that, if you like.

        The first hidden text box contains a course description.  To see it, click on the text box with the secondary mouse button.  This description will appear on the web pages created by both getgrades.cgi and hist.cgi.  The present value is “The GetGrades Software Demo”; change it to match your needs, perhaps something like “Dr. Seuss’ 10 AM Writing Class”.  The second hidden text box contains the course id.  The current value is “KingDemo”.  Change it to match a course id you entered in getgrades.cgi


        One final touch is needed.  Notice that the hidden text boxes are underlined with a dotted line, which means their “hidden” property in the font dialog box is set to true.  Makes sense, but Netscape browsers will ignore hidden text boxes that are “hidden” (Internet Explorer, of course, doesn’t have a problem with this).  So select both hidden text boxes, go to the font dialog box (on the Tools menu) and uncheck the “hidden” box.  Now the Netscape browser will work.


        That’s all there is to it, if you have just one section of the course.  If you have more than one section, you can add option buttons to select which section to get grades for.  If you have multiple sections, copy the following into a Word document.


Copy from here


All of your grades may be viewed.  Select your section, then click on the button to go to the password page.

Monday afternoon lab

Tuesday afternoon lab



To here.  (Clicking on the button won’t be exciting, because you don’t have the password.)


This example has does not use hidden text fields.  After entering design mode, look at the properties of the first option button (see previous example for how to do this).  It’s value is “KingOrgILab_sec1/Dr. King's Monday Organic I Lab”.  Both the course id and the course description are entered on the same line, separated by the /.  Only one backslash should be present in this line.  The second option button is for the second section.  Its value is “KingOrgILab_sec2/Dr. King's Tuesday Organic I Lab”.  Construct values appropriate for your needs.  The format is “course id/course description”.  The command button is set up exactly as in the previous example. 



        The final step is setting up your spreadsheet.


Description               Download & Installation               Setting Up a Spreadsheet



Page last updated on May 5, 2005.  Send comments to Chris King.