To automate evals, using Excel and Word

1. STEP 1 Open Excel and create Excel file for course data
   1. copy list of students (from AIS) into cell A3. This list needs to be broken into appropriate columns
   2. go to “Data” menu in Excel, and down to “text to columns,” in order to break the list of students into columns.
   1. choose “delimited” (data from AIS are delimited by commas); click on “Next”
   2. choose “commas;” notice in the “test to columns” window that the text file of students is formatted into columns. Click on “finish.”
   3. next, back in your original Excel file, clean up your file of students into columns you really need, by removing certain columns (such as student number, email info, etc. Keep just enough to recognize the student, including “gender” if you plan to automate he/she and him/her usage later on).
   4. add headers to the columns containing (eventually) the students’ results. Examples: LNAME / FNAME / GENDER / GOES BY / OVERALL / WORKBOOK / QUIZZES / FINAL / ORAL / ATTENDANCE / ETC...
   5. enter students’ results into appropriate cells in your Excel file (e.g. 97 for Joe Dupont in QUIZZES).
   6. save Excel file.

2. STEP 2 Use Word text program to create single template for class that will merge data from the Excel file just created. An example of template is given at the end of this page.
   1. open “data merge manager” palette (in TOOLS menu)

      

   2. in “data merge manager” palette, click on “create” in “main document”.
      1. choose “catalog”
      2. then go to “data source” (still in “data merge manager”) and click on “get data.” Select “Open data source” since it already exists: choose the Excel file you created above in STEP 1.
      3. click ok in window asking about Sheet 1.
      4. in the Word window (now connected in the background to the Excel file), type text of Template, using “Merge field” in “data merge manager” palette to insert the titles of columns from the Excel file. The “Merge field” drop menu provides you with all the column headers from the Excel file you’ll be merging to. Drag these items one by one to where you want them in your template. E.g.: GOES BY’s performance was OVERALL. GOES BY’s results on quizzes were QUIZZES. etc... Put a space (CR) at the bottom of your template.
      5. Once you’re moderately satisfied with this draft of a new template (it is best to think of it as a draft at this time, and focus on the mechanics of processing it, since you’ll be able to change it and improve on it at will), go to the “Merge” drop menu in the “data merge manager” and click on “merge to new document” (second button). This will produce a text document which you can save under a new name and edit at will.
      6. At this point, you may discover that your basic template is too basic: just repeat the steps above and re-write it, before running the “merge to new document” again, to produce a more satisfactory text. Note that the entry for the first student of your eval file may be in second position. Just edit out the text above it.
      7. Once you’re happy with the results, you can paste the evals into the AIS system directly.
      8. You may want to play with the “data merge manager” and automate even further. E.g., it is possible to automate “he/she” pronouns and “her/his” possessives in response to the M/F gender column of the Excel file.

   APPENDIX: EXAMPLE OF TEMPLATE

   «LNAME», «ID», Overall, «GOES_BY»’s performance indicated «OVERALL» understanding and control of the material covered in the first two units (two books) of the course handbook (up to and including chapter xx). «GOES_BY» received a combined average of «QUIZZES» on the eight quizzes given in class. All quizzes were done without dictionary or grammar and consisted of translations of new stories, vocabulary, and cultural questions. She got a score of «FINAL» on the final exam. The homework, which required doing many exercises and translations in both the books and workbooks, was «HOMEWORK». She often did the supplementary exercises recommended in class, and the presentation of all of her work was excellent. Participation in class and in group work was «PARTIC».

   ---

   Edited on Sat, Mar 25, 2006 (GH)