Readable Document Variables

Figure 19-8 displays each document variable using text that represents the name of the corresponding Access field name in angle brackets. When you enter references to document variables in field codes in a document, the variables have no value and do not display any text. You can assign text values to the document variables to make the document more readable and easier to edit, by running the following code:

Sub AddVariables()

Dim sFolder As String

Dim con As New ADODB.Connection

Dim rs As New ADODB.Recordset

Dim fld As ADODB.Field

Dim wdApp As Word.Application

Dim doc As Word.Document

'Open instance of Word and make it visible Set wdApp = New Word.Application wdApp.Visible = True

'Get directory of this file sFolder = ThisWorkbook.Path

'Open the template letter in same directory

Set doc = wdApp.Documents.Open(Filename:=sFolder & "\Letter.docx")

With doc

'Connect to database in same directory con.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _

"Data Source=" & sFolder & "\MailMerge.accdb;"

'Open agents table

rs.Open "tAgents", con

'Define document variables

with name identical to field name

'and value equal to field


For Each fld In rs.Fields

.Variables(fld.Name) = '

'<" & fld.Name & ">"

Next fld


'Repeat operation for courses attended query

rs.Open "qCoursesAttended"

, con

For Each fld In rs.Fields

.Variables(fld.Name) =

"<" & fld.Name & ">"

Next fld

.Fields.Update End With End Sub

The only substantial difference in this code, compared with CreateLetters, is that you open the document to edit it using the Open method instead of using the Add method to create a copy of the document. When creating the document variables, you assign them values that consist of the field names in angle brackets.

0 0

Post a comment