Mail Merge Word – using VBA to split a Word doc and name it

It’s back to school time and as usual I’m extracting data from Socrative reports.
Socrative.com is great when it comes to auto marking multiple choice content but if I have a few manually marked questions then it becomes painful in both adding up the marks and giving the students feedback as to where they can improve.

My method for doing it at the moment is

1. Extracting the Socrative Excel spreadsheet for the report and formatting it so it has no word wrap and the question columns are all the same size of square
2. After each of the manually marked columns, I add a new column and colour it bright yellow
3. I then go through the manual answers for each students and add their points in this new column
4. I adjust the totals at the start so that it adds up these extra points

A bit of a faff but I can do this relatively quickly.

Today’s task though was more tricky as I wanted to give the students the marks I’d given them in yellow back to them.

So I ended up doing a mail merge! I know! Very old school.

I created this Word document:

..and merging a few fields from the spreadsheet into it (name, total, total for each of the 4 manual sections and their answers to these) I managed to make it look like below

So far so good, however Word’s Mail Merge puts all the “letters” into one Word doc, which is a pain when you want to attach the individual feedback to each student’s profile on the VLE)

So I did a quick google of how to automatically split the doc on page breaks and found this helpful post showing how to make a Word VBA macro to split on new pages (https://word.tips.net/T001538_Merging_to_Individual_Files.html)

But unfortunately it just used numbers to name the documents it produces – not helpful when I have 3 classes of 20 students and some were absent so I can’t guarantee what order they will be in.

After much Googling I modified the original macro to find the student’s name in the first table cell, chop off the last two characters (line feeds I think) and, et voila, it splits one doc into many docs all named the names of my lovely students!

Macro is here:

Sub BreakOnSection()
    'Used to set criteria for moving through the document by section.
    Application.Browser.Target = wdBrowseSection

    'A mailmerge document ends with a section break next page.
    'Subtracting one from the section count stop error message.
    For i = 1 To ((ActiveDocument.Sections.Count) - 1)

        'Select and copy the section text to the clipboard
        ActiveDocument.Bookmarks("\Section").Range.Copy

        'Create a new document to paste text from clipboard.
        Documents.Add
        Selection.Paste

        'Removes the break that is copied at the end of the section, if any.
        Selection.MoveUp Unit:=wdLine, Count:=1, Extend:=wdExtend
        Selection.Delete Unit:=wdCharacter, Count:=1

        Dim t As Table
        For Each t In ActiveDocument.Tables
          MyName = t.Cell(1, 1).Range.Text

        Next t
        MyName = Left(MyName, Len(MyName) - 2)



        ChangeFileOpenDirectory "C:\Users\XXX\Downloads\"
        DocNum = DocNum + 1
        ActiveDocument.SaveAs FileName:="9D AI _" & MyName & "_" & DocNum & ".doc"
        ActiveDocument.Close
        'Move the selection to the next section in the document
        Application.Browser.Next
    Next i
    ActiveDocument.Close savechanges:=wdDoNotSaveChanges
End Sub

And this shows some of the documents it has produced.

There probably is a much simpler way – and I’m sure the hours I’ve spent on this today I could have manually written the marks on their documents but still at least this is here for posterity in case anyone else needs to use it.