So, come in to work today and I am asked to produce 83 Exhibit Note Certificates. 83. I cannot palm this off because the admin assistant is off sick with the flu, and I cannot push the task back any further because the affidavit and its exhibits are due today.
I’m going to be busy enough engrossing and witnessing the affidavit, sorting out the actual exhibits, and certifying each Exhibit Note.
So I think, what is the quickest way of producing these documents? It’s for New South Wales, and unlike here, I don’t actually have to describe each document on the face of each Exhibit Note…the only variable then is the exhibit number…and guess what? Word has a perfectly good counter built into it.
No, I am not talking about mail merge, but rather Visual Basic for Applications.
Going off memory the process is to:-
- draft a model exhibit note certificate, with the date filled and my details filled in
- use insert two Document Variables, “ExhibitNo” and “ExhibitNoDup” , where appropriate in the document
- check that the default printer is set correctly in the printer control panel; and
- use the Macros -> Visual Basic Editor write and run this little program:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
| Sub Produce_Many_Exhibit_Notes()
Dim intStart As Integer
Dim intEnd As Integer
Dim intTotal As Integer
Dim i As Integer
intStart = InputBox("Start Exhibit Note Number:", "by Christopher Speck 25 May 2010", 1)
intEnd = InputBox("Ending Exhibit Note Number:", "by Christopher Speck 25 May 2010", 83)
If intStart = 0 Then
MsgBox "Start must be equal to or greater then one", vbExclamation
Exit Sub
End If
If intEnd < intStart Then
MsgBox "Ending number equal to or greater then start number", vbExclamation
Exit Sub
End If
intTotal = intEnd - intStart + 1
If MsgBox("Continuing will print " & intTotal & " Exhibit Notes from " & vbCrLf & _
intStart & " to " & intEnd & " and print them to your default" & vbCrLf & _
" printer. Are you sure you wish to continue?", vbQuestion + vbYesNo) = vbYes Then
For i = intStart To intEnd
'increment document variables ExhibitNo and ExhibitNo_Dup
ThisDocument.Variables("ExhibitNo").Value = i
ThisDocument.Variables("ExhibitNo_Dup").Value = i
ThisDocument.Fields.Update
DoEvents
ThisDocument.PrintOut
DoEvents
'for debugging purposes only
'If MsgBox("Continue?", vbQuestion + vbYesNo) = vbNo Then
' Exit For
'End If
Next i
MsgBox "Process complete", vbInformation
End If
End Sub |
I wrote that program above. All up this process to me about 25 minutes to produce and print the 83 Exhibit Notes…a lot better then trying to use mail merge or manually incrementing each sheet, printing, and doing the same again.