REM ********** This macro processes the Excel file produced by the Blackboard grade extract and saves it in the computer's default file location as a comma-separated text file called "Grades.txt" 
REM ********** Version 9, S. Birmingham, 3/6/12

REM *** The date/time the file was produced is in the last cell of column D. Store that value in a variable to display later

Dim runtime as string
runtime = Range("D1").End(xlDown).value

REM *** Prompt for year and term, create selection criteria in the format "<>*myYear_myTerm*"

myYear = InputBox(Prompt:="Enter the year you wish to export", Title:="Academic year selection", Default:="2011")
myTerm = InputBox(Prompt:="Enter the term you wish to export", Title:="Academic term selection", Default:="SU")

if myYear < 2010 or myYear > 2050 or myYear = "" or myTerm="" then 
proceed = MsgBox("Invalid year/term." ,vbExclamation, "Warning")
end
end if

myYearTerm = "<>*" & myYear & "_" & myTerm & "_*"

REM *** Delete all columns except (EXTERNAL_PERSON_KEY, EXTERNAL_COURSE_KEY, COURSE_TITLE, ENROLLMENT_AVAILABLE, EXTERNAL_GRADE, DISPLAY_COLUMN_NAME, DISPLAY_GRADE)

Range("A:A,C:AA,AC:AF,AH:AH,AJ:AJ,AM:AS,AU:AX").Delete Shift:=xlToLeft

REM *** Delete rows where the EXTERNAL_COURSE_KEY doesn't contain myYearTerm

Columns("A:G").AutoFilter Field:=2, Criteria1:= myYearTerm
Selection.SpecialCells(xlCellTypeVisible).EntireRow.Delete

REM *** Delete rows where ENROLLMENT_AVAILABLE = "N" (students who have dropped the class) then delete that column

Range("A1").EntireRow.Insert     ' Insert placeholder row which will be deleted by the following autofilter statement
Range("A1")="Placeholder row"
Columns("A:G").Select
Selection.AutoFilter Field:=4, Criteria1:= "N"
Selection.SpecialCells(xlCellTypeVisible).EntireRow.Delete
Columns("D:D").Delete

REM *** Sort by EXTERNAL_COURSE_KEY then EXTERNAL_GRADE

Range("A1:F1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Key2:=Range("D1"), Order2:=xlDescending

REM *** Loop through all rows and mark any course with no external grade column set

Range("A1").EntireRow.Insert     ' Insert placeholder row
Range("A1")="Placeholder row"

For i = 1 To Range("A1000000").End(xlUp).Row-1

If Cells(i+1, 2) <> Cells(i, 2) Then
  If Cells(i+1, 4) <> "Y" Then
    Cells(i+1, 4) = "Y"
    Cells(i+1, 5) = "# external column not set #"
    Cells(i+1, 6) = "n/a"
    Range(Cells(i+1, 1), Cells(i+1, 6)).Font.Color = -16776961
  End If
End if

Next i

REM *** Delete rows where EXTERNAL_GRADE = "N"

Columns("A:F").Select
Selection.AutoFilter Field:=4, Criteria1:="N"
Selection.SpecialCells(xlCellTypeVisible).EntireRow.Delete

REM *** Delete the EXTERNAL_GRADE column

Range("D:D").Delete Shift:=xlToLeft

REM *** Loop through all rows and mark invalid grades red

Range("A1").EntireRow.Insert     ' Insert placeholder row
Range("A1")="Placeholder row"

validgrades = Array("A", "A-", "B+", "B", "B-", "C+", "C", "C-", "D+", "D", "D-", "F", "AU", "I", "L", "P", "T", "W", "Y")

For i = 1 To Range("A1000000").End(xlUp).Row-1

if Cells(i+1,2)<>Cells(i,2) then courses=courses+1

validity=false

For j = 0 To UBound(validgrades)

If Cells(i+1, 5) = validgrades(j) Then

validity=true

Exit For

End If

Next j

if validity=false then 

if cells (i+1,5)="" then cells(i+1,6)="Grade must not be blank" else if cells(i+1,5)="n/a" then cells(i+1,6)= "<-- no grades exported for this section" else cells(i+1,6)="Grade must be a letter"
Range(cells(i+1,1),cells(i+1,6)).font.color= -16776961

End if

Next i

REM *** Create a duplicate of the worksheet to hold invalid grades

ActiveSheet.Copy After:=Sheets(1) 
Sheets(2).Name = "Invalid Grades"
Sheets(1).Name = "Valid Grades"

REM *** Delete everything but invalid grades from the "invalid grades" worksheet

Sheets("Invalid Grades").Activate
Columns("A:F").Select
Selection.AutoFilter Field:=1, Operator:=xlFilterAutomaticFontColor
Selection.SpecialCells(xlCellTypeVisible).EntireRow.Delete

Range("A1").EntireRow.Insert     ' Insert placeholder row which will be deleted by the following autofilter statement
Range("A1")="Placeholder row"

REM*** Create a duplicate of the "Invalid Grades" worksheet to hold only sections with no external column set

ActiveSheet.Copy After:=Sheets(2) 
Sheets(3).Name = "Ext Col Not Set"

Columns("A:F").AutoFilter Field:=6, Criteria1:= "=Grade must be a letter", Operator:=xlOr, Criteria2:= "=Grade must not be blank"
Selection.SpecialCells(xlCellTypeVisible).EntireRow.Delete

Range("A1").EntireRow.Insert
Range("A1").EntireRow.Insert
Range("A1").EntireRow.Insert

Range("A:A,D:F").Delete Shift:=xlToLeft

Range("A3")="Section ID"
Range("B3")="Section Name"
Range("A1:B3").Font.Bold = True

Columns("A:B").AutoFit

Range("A1")="No grades have been exported for these sections because no column has been set as the external grade in the Grade Center"

REM *** Delete courses with no external column set from the "Invalid Grades" sheet, count the sections with invalid grades, apply formatting

Sheets("Invalid Grades").Activate
Columns("A:F").Select
Selection.AutoFilter Field:=4, Criteria1:= "=# external column not set #"
Selection.SpecialCells(xlCellTypeVisible).EntireRow.Delete

For i = 1 To Range("A1000000").End(xlUp).Row
if Cells(i+1,2)<>Cells(i,2) then invalid=invalid+1
Next i

Range("A1").EntireRow.Insert
Range("A1")="Student ID"
Range("B1")="Section ID"
Range("C1")="Section Name"
Range("D1")="Column set as external grade"
Range("E1")="Grade"
Range("F1")="Notes"
Range("A1:F1").Font.Bold = True
Columns("A:F").AutoFit

REM *** Delete everything but valid grades from first sheet

Sheets("Valid Grades").Activate
Columns("A:F").Select
Selection.AutoFilter Field:=4, Criteria1:=RGB(255, 0, 0), Operator:=xlFilterFontColor
Selection.SpecialCells(xlCellTypeVisible).EntireRow.Delete

Range("C:D,F:F").Delete Shift:=xlToLeft
Range("A1").EntireColumn.Insert
Columns("C:C").Cut
Columns("A:A").Select
ActiveSheet.Paste
Columns("C:C").Delete Shift:=xlToLeft

Range("A1").EntireRow.Insert
Range("A1")="Section ID"
Range("B1")="Student ID"
Range("C1")="Grade"
Range("A1:C1").Font.Bold = True
Columns("A:C").AutoFit

REM *** Create comma-separated text file suitable for import into Jenzabar CX then reformat the valid grades sheet

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= "grades.txt", FileFormat:=xlCSV, CreateBackup:=False
ActiveSheet.Name = "Valid Grades"

Range("A1")="Section ID"
Range("B1")="Student ID"
Range("C1")="Grade"
Range("A1:C1").Font.Bold = True
Columns("A:C").AutoFit

REM *** Create summary page with results

Sheets.Add Before:=Sheets(1)
Sheets(1).Name = "Summary"

Range("A1")="Grade Process Results for " & myTerm & " " & myYear
Range("A1").Font.Bold = True

Range("A2")="data from Blackboard as of " & mid(runtime,5,2) & "/" & mid(runtime,7,2) & "/" & mid(runtime,3,2) & " at " & mid(runtime,10,5)
Range("A2").Font.Italic = True

Range("A5")="Sections exporting at least one invalid grade (blanks, numbers, etc.):"
Range("I5")=invalid+0

Range("A6")= "Sections exporting no grades (external grade column not set):"
Range("I6").Formula="=COUNTA('Ext Col Not Set'!A:A)-2"

Range("A4")="Sections exporting perfectly:"
Range("I4")=courses-Range("I5").Value-Range("I6").Value

Range("G7")="Total sections"
Range("I7").Formula="=I4+I5+I6"
Range("A6:I6").Borders(xlEdgeBottom).Weight = xlThin

Range("A9")="Number of valid grades in original file:"
Range("I9").Formula="=COUNTA('Valid Grades'!A:A)-1"

Range("A10")="Number of invalid grades in original file:"
Range("I10").Formula="=COUNTA('Invalid Grades'!A:A)-1"

Range("G11")="Total grades"
Range("I11").Formula="=I9+I10"
Range("A10:I10").Borders(xlEdgeBottom).Weight = xlThin

Range("A13")="Valid grades have been saved to the file " & Application.DefaultFilePath & "\grades.txt"