Datasheet

Book IX
Chapter 1
Automation with Other
Office Programs
675
Merging Data with a Word Document
‘Open Word using template and make Word visible
Wrd.Documents.Add sMergeDoc
Wrd.Visible = True
‘Replace Bookmarks with Values
With Wrd.ActiveDocument.Bookmarks
.Item(“CurrentDate”).Range.Text = Date
.Item(“AccessAddress”).Range.Text = sAccessAddress
.Item(“AccessSalutation”).Range.Text = sAccessSalutation
End With
‘Open in PrintPreview mode, let user print
Wrd.ActiveDocument.PrintPreview
‘Clean Up code
Set Wrd = Nothing
End Sub
Again, there’s a lot of code here, but it breaks down into a number of differ-
ent sections. We declare the variables we’re going to use, set the address
and salutation variables, open Word using the template we created, replace
the bookmarks with values from Access, and show the Print Preview view for
the document. Let’s take a closer look at some key components of this code.
After declaring the string values, we set the sAccessAddress variable to
a concatenated string of values from the form. We use the line continuation
character (an underscore) as well as the vbCrlf keyword, which starts a
new line in the string variable:
‘Build sAccessAddress
sAccessAddress = FirstName & “ “ & LastName & _
vbCrLf & Company & vbCrLf & Address1 & _
vbCrLf & City & “, “ & StateProv & “ “ & ZIPCode
We also build the sAccessSalutation variable by combining just the first
and last name fields on the form with a space in between:
‘Build sAccessSalutation
sAccessSalutation = FirstName & “ “ & LastName
Next, we use the syntax described earlier in this chapter to open an instance
of Microsoft Word. Here’s the code:
‘Declare and set Word object variables
Dim Wrd As New Word.Application
Set Wrd = CreateObject(“Word.Application”)
In order to control Word 2010 using VBA, you must first add the Microsoft
Office Word 14.0 Object Library to VBA using the References window.
44_532188-bk09ch01.indd 67544_532188-bk09ch01.indd 675 3/29/10 10:59 PM3/29/10 10:59 PM