Many schools create one text document for each student. They then ask the teacher to enter grades on the local school network. An example in point: my present school.
This has some obvious disadvantages:
- If the school’s network is down no grades can be entered. This just happened at my school. The server was down for three weeks.
- Since the data is collected in a text document and not in a database table analysis of the data is not feasible.
- It is difficult to change the layout or text on the report when grades have been entered.
- Two teachers can not enter grades on the same student at the same time.
- By mistake teachers may create havoc to the report layout or content.
- The teachers have to wade through all students to find the ones they teach. They may miss someone and/or enter grades under the wrong subject heading. The teachers have no way of knowing that they have entered all the grades.
Here are its major advantages:
- It is rather easy to create the empty reports using mail merge, or even by adding the students’ names manually and naming the text document with their name.
- Teachers’ grades and tutors’ comments can be entered in the same document.
Better ways of doing this do exist, of course, and some of them does not cost a fortune, but more about that later. Now I want to deal with disadvantage 3 above:
It is difficult to change the layout or text on the report when grades have been entered.
155 empty report cards had been put on the local server when thee mistakes were noted.
- The school’s name is “Academy St James” (fictional), but had been entered as “Academy of St James”.
- The text “Absent:” had not been put in bold. “Lates:” was correctly entered in bold.
- “classwork” should have been “class work”.
If the empty reports were created by mail merge, (I don’t know how they were created and there was no time to find out), one could easily fix these mistakes in the source document and merge again with the data. But if the 155 reports were created manually, or if these mistakes had been discovered after some or all of the grades had been entered, then what?
An obvious solution would be to manually fix the errors in all 155 documents. This could take any time from one to three hours and afterwards one might not remember the capital of France.
A more attractive solution was to write a macro that would do all the corrections automatically and that could be used if/when other mistakes, changes were called for. Since the reports were Word documents I made a Word macro.
The first task was to reinstall MS Word 2003 on my netbook. I had just made an oath not to use MS Office again, but this was an emergency. (I am more than happy with the free Open Office.)
The report cards were divided into folders. One folder per tutor group. Each folder had a document for each students. About 15-25 documents per folder.
My macro would ask me for the folder and perform the changes on all the documents in the folder. I would run the macro for the seven folders.
To ask the user for a folder, as opposed to a file, requires quite a lot of instructions in VBA (Visual Basic for Applications). I found usable code quite easily on the internet.
I put this code in General – Declarations:
Private Declare Function SHGetPathFromIDList Lib “shell32.dll” _ Alias “SHGetPathFromIDListA” (ByVal pidl As Long, ByVal pszPath As String) As LongPrivate Declare Function SHBrowseForFolder Lib “shell32.dll” _ Alias “SHBrowseForFolderA” (lpBrowseInfo As BROWSEINFO) As LongPrivate Type BROWSEINFO ‘ used by the function GetFolderName hOwner As Long pidlRoot As Long pszDisplayName As String lpszTitle As String ulFlags As Long lpfn As Long lParam As Long iImage As LongEnd TypePublic SubmissionStatus As StringPublic ValidationStatus As StringPublic Comp As StringPublic FolderName As String
The line breaks did not work out, but you get the gist.
Then a function was needed:
Function GetFolderName(Msg As String) As String’ returns the name of the folder selected by the userDim bInfo As BROWSEINFO, path As String, r As LongDim X As Long, pos As Integer bInfo.pidlRoot = 0& ‘ Root folder = Desktop If IsMissing(Msg) Then bInfo.lpszTitle = “Select a folder.” ’ the dialog title Else bInfo.lpszTitle = Msg ‘ the dialog title End If bInfo.ulFlags = &H1 ‘ Type of directory to return X = SHBrowseForFolder(bInfo) ‘ display the dialog ’ Parse the result path = Space$(512) r = SHGetPathFromIDList(ByVal X, ByVal path) If r Then pos = InStr(path, Chr$(0)) GetFolderName = Left(path, pos – 1) Else GetFolderName = “” End IfEnd Function
And finally a procedure:
Sub TargetFolderName() FolderName = GetFolderName("Select the Folder to Scan...") If FolderName = "" Then MsgBox "You didn't select a folder. Please try again." End End If End Sub
Puuh! All that is needed for the simple call:
Sub test01() ' ask the user for a folder cfolder = GetFolderName("Please choose a folder")
The next step is to open each document in turn:
Dim fsSearch As FileSearchSet fsSearch = Application.FileSearch With fsSearch .FileName = "*.doc" .LookIn = cfolder .Execute If .FoundFiles.Count = 0 Then MsgBox "No Word documents were found in " + cfolder Else For i = 1 To .FoundFiles.Count Documents.Open FileName:=.FoundFiles(i) ' code for making changes to the document just opened ActiveDocument.Close Savechanges:=wdSaveChanges Next i End If End With MsgBox "fin" End Sub
To make changes are easy. Just add code below the comment in red above.
Change “Academy of St James” to “Academy St James”:
For Each myStoryRange In ActiveDocument.StoryRanges With myStoryRange.Find .Text = "Academy of St James" .Replacement.Text = "Academy St James" .Wrap = wdFindContinue .Execute Replace:=wdReplaceAll End With Next myStoryRange
The code is most likely overkill, but it works. To put text in bold I used this code:
' set a word in bold Selection.Find.ClearFormatting With Selection.Find .Text = "Absent:" .Replacement.Text = "" .Forward = True .Wrap = wdFindContinue .Format = True .MatchCase = False .MatchWholeWord = False .MatchKashida = False .MatchDiacritics = False .MatchAlefHamza = False .MatchControl = False .MatchWildcards = False .MatchSoundsLike = False .MatchAllWordForms = False End With Selection.Find.Execute Selection.Font.Bold = True
Again, the code has probably too many instructions, but that is a minor concern in this case.
Next post will try to put the Word documents online as GoogleDocs documents.