Close Microsoft Word

That takes care of the Word document. The rest of the action takes place in Access and VBA.

Creating the Access form

For this example, we create a simple form that's bound to a table named Customers, with the usual name and address fields that you would expect to find in such a table. Figure 14-8 shows a sample form in Form view. The controls that are relevant to the form letter start at the one containing the name Tori. Starting at that field and reading down and to the right, the names of the controls are

FirstName

LastName

Company

Address1

Address2

City

State

In the VBA code to follow, you see the names referred to with square brackets — [FirstName], [LastName], and [Company], for example. The e-mail address control, near the bottom, isn't really relevant to the topic at hand. The drop-down list near the top of the control provides the user with a means of choosing a customer. The Merge to Word Letter button is named MergeBttn.

Figure 14-8:

Create a form to display one customer's name and address at a time.

Figure 14-8:

Create a form to display one customer's name and address at a time.

To use the form, a person chooses a name from the Choose a Customer combo box and then clicks the Merge to Word Letter button. That button executes VBA code to open the document template, replaces each bookmark with data from the current record in the form, prints the document, and then closes Word.

Writing the merge code

For this example, we place the code in the form shown in Figure 14-8 by attaching it to the MergeBttn control's On Click event. The VBA code is shown in its entirety in Listing 14-2.

Listing 14-2: Merging Data with a Word Document

Private Sub MergeBttn_Click()

'Declare variables for storing strings (text). Dim AddyLineVar As String, SalutationVar As String

'Start building AddyLineVar, by dealing with blank 'LastName and Company fields (allowed in this table). If IsNull([LastName]) Then AddyLineVar = [Company]

'Just set SalutationVar to generic "Sir or Madam". SalutationVar = "Sir or Madam" Else

AddyLineVar = [FirstName] & " " & [LastName] 'If the Company isn't blank, tack that on after name.

If Not IsNull([Company]) Then

AddyLineVar = AddyLineVar & vbCrLf & [Company] End If

'Salutation will be customer's first name. SalutationVar = [FirstName] End If

'Add line break and Addressl

AddyLineVar = AddyLineVar & vbCrLf & [Addressl]

'If Address2 isn't null, add line break and Address2 If Not IsNull([Address2]) Then

AddyLineVar = AddyLineVar & vbCrLf & [Address2] End If

'Tack on line break and then City, State, Zip. AddyLineVar = AddyLineVar & vbCrLf & [City] & ", " AddyLineVar = AddyLineVar & [State] & " " & [ZIP]

'Declare an instance of Microsoft Word.

Dim Wrd As New Word.Application

Set Wrd = CreateObject("Word.Application")

'Specify the path and name to the Word document. Dim MergeDoc As String

MergeDoc = Application.CurrentProject.Path MergeDoc = MergeDoc & "\WordFormLetter.dotx"

'Open the document template, make it visible. Wrd.Documents.Add MergeDoc Wrd.Visible = True

'Replace each bookmark with current data. With Wrd.ActiveDocument.Bookmarks

.Item("TodaysDate").Range.Text = Date .Item("AddressLines").Range.Text = AddyLineVar .Item("Salutation").Range.Text = SalutationVar End With

'Letter is ready to print, so print it. Wrd.ActiveDocument.PrintOut

'All done. Close up (no need to save document) Wrd.ActiveDocument.Close wdDoNotSaveChanges Wrd.Quit

End Sub

Like all procedures, this one is just a series of small steps carried out in a specific order to achieve some goal. The first line tells you that this procedure executes whenever a user clicks the MergeBttn button:

Private Sub MergeBttn_Click()

The next two lines declare two string variables named AddyLineVar and SalutationVar. Each of those variables becomes a string of text to be substituted into the document template in place of the AddressLines and Salutation bookmarks:

'Declare variables for storing strings (text).

Dim AddyLineVar As String, SalutationVar As String

In the Customers table we use for this example, the Addressl, City, State, and ZIP code fields are required, but the user can leave the FirstName, Company, and Address2 fields empty (Null). The code that follows builds the variable AddyLineVar as needed for whatever information is available in the current record. The first big If...End If block, shown next, starts out by saying "If the LastName field for this record is empty, make the first line of the AddyLineVar the company name and make SalutationVar into the general title, Sir or Madam:."

'Start building AddyLineVar, by dealing with blank

'LastName and Company fields (allowed in this table).

If IsNull([LastName]) Then

AddyLineVar = [Company]

'Just set SalutationVar to generic "Sir or Madam".

SalutationVar = "Sir or Madam"

If the LastName field for this record is not null, the following code adds the customer's first and last names to AddyLineVar. Note that vbCrLf is the Access VBA constant for a carriage return or linefeed. Each vbCrLf in AddyLineVar translates to the end of the line in the Word document. Note, too, that SalutationVar gets its value from the FirstName field in the following code:

Else

AddyLineVar = [FirstName] & "

" & [LastName]

'If the Company isn't blank,

tack that on after name.

If Not IsNull([Company]) Then

AddyLineVar = AddyLineVar

& vbCrLf & [Company]

End If

'Salutation will be customer'

s first name.

SalutationVar = [FirstName]

End If

Because Addressl is a required field, you can assume that it is not null. The following code adds a vbCrLf and the contents of the Addressl field to AddyLineVar:

'Add CRLF and Addressl

AddyLineVar = AddyLineVar & vbCrLf & [Addressl]

The next If...End If block adds a line break and the contents of the

Address2 field to AddyLineVar but only if the Address2 field isn't empty:

'If Address2 isn't null, add CRLF and Address2 If Not IsNull([Address2]) Then

AddyLineVar = AddyLineVar & vbCrLf & [Address2] End If

Because the City, State, and ZIP fields are required in the Customers table, the next lines of code just add another vbCrLf to AddyLineVar, followed by the City, a comma and blank space (, ), the State, two blank spaces, and then the ZIP:

'Tack on line break and then City, State, Zip. AddyLineVar = AddyLineVar & vbCrLf & [City] & ", " AddyLineVar = AddyLineVar & [State] & " " & [ZIP]

At this point in the code, the AddyLineVar and SalutationVar variables both contain the data to be plugged into the form letter. Now you can start writing the code to open Word and replace its bookmarks with some actual data. First, use the standard syntax described earlier in this chapter to declare and open an instance of Microsoft Word:

The VBA Editor doesn't accept the statements that follow if you haven't already selected the Microsoft Word 12.0 Object Library in your References dialog box:

'Declare an instance of Microsoft Word.

Dim Wrd As New Word.Application

Set Wrd = CreateObject("Word.Application")

From this point in the code, the object variable named Wrd refers to an open instance of Microsoft Word (and its entire Application object, which exposes all of Microsoft Word to VBA).

The next step is to open the document template that contains the bookmarks and text. The syntax for opening a Word document from Access VBA is objVar.Documents.Add path. The objVar must match the object variable used in the Dim and Set statements (Wrd in this example).

The path must be the complete path to the Word document. In our example, we place the Word document in the same folder as the database ACCDB file (which makes it easy to find). In Access, you can use Application. CurrentProject.Path to get the path to the open database. We named our Word document WordFormLetter.dotx. The following statements create a string variable named MergeDoc that contains the full path and filename of that Word document template:

'Specify the path and name to the Word document. Dim MergeDoc As String

MergeDoc = Application.CurrentProject.Path MergeDoc = MergeDoc & "\WordFormLetter.dotx"

If WordFormLetter.dotx were in some other folder, we couldn't use Application.CurrentProject.Path to get its path. We would have to specify the path literally in the code. For example, in Windows, if your user account name is Bobo and your form letter is named MyFormLetter.dotx and is stored in your My Documents folder, the following statement works just fine as long as you type it into the Code window as one long line:

Wrd.Documents.Add "C:\Documents and Settings\Bobo\My Documents\MyFormLetter.dotx"

Normally, when VBA opens an instance of Word, the program window is invisible, and all activity takes place behind the scenes. For testing and debugging purposes, though, you probably want to make Word visible so that you can see what's happening. To make the window visible, set its Visible property to True, as shown here:

Wrd.Visible = True

When Word and a document are both open, VBA can refer to the document as objVar.ActiveDocument (where, once again, objVar matches the object variable name, which is Wrd in this example). The ActiveDocument object, in turn, contains a Bookmarks collection, which contains a list of all bookmarks in the document.

This statement begins a With...End With block that defines the current document's Bookmarks collection (Wrd.ActiveDocument.Bookmarks) as the item to which all properties to follow (up to End With) are applied:

'Replace each bookmark with current data. With Wrd.ActiveDocument.Bookmarks

Within the With...End With block, you can refer to any bookmark by name by using the syntax .Item(bookmarkName) where bookmarkName is the name of the bookmark as defined in the Word document. Each bookmark has a .Range property, which refers to everything that's contained within the bookmark. The .Range property in turn has a .Text property, which refers specifically to the text within the bookmark. Thus, the statement

.Item("AddressLines").Range.Text = AddyLineVar says "Change whatever text is currently in the bookmark named AddressLines to whatever is currently in the variable named

AddyLineVar."

In the following code, we change the TodaysDate bookmark to the current date, the AddressLines bookmark to the contents of the AddyLineVar variable, and the Salutation bookmark to whatever is in the variable named SalutationVar:

.Item(

"TodaysDate")

.Range.Text =

Date

.Item(

"AddressLines

").Range.Text

= AddyLineVar

.Item(

"Salutation")

.Range.Text =

SalutationVar

End With

On-screen, the document template now contains the complete form letter with all the right information. This next statement prints the form letter:

'Print the letter. Wrd.ActiveDocument.PrintOut

The following statement closes the letter without saving it. (You don't need to save the letter after it's printed because you always have the document template to work with.)

'All done. Close up (no need to save document) Wrd.ActiveDocument.Close wdDoNotSaveChanges

These two lines close Microsoft Word and end the procedure:

Wrd.Quit End Sub

Figure 14-9 shows an example of using the procedure. There we chose a customer named Tori Pines from the Access form and then clicked the Merge to Word Letter button. The form letter that you see in the background is the result, with the date, address lines, and salutation all in place. The code is written to print the letter, so you would never see the form on top of the Word document. (We had to superimpose the form there.) However, you would get a printed copy of the letter shown in the figure.

334 Part V: Reachin9 0ut with VBA

Figure 14-9:

Record from the form merged into a form letter.

Figure 14-9:

Record from the form merged into a form letter.

Was this article helpful?

0 -1

Post a comment