Sending Data to Microsoft Word

There are plenty of ways to print Access data without getting into VBA. The usual method is to just create a report from the Access database window. You can also use the Microsoft Word Mail Merge Wizard to print form letters, envelopes, labels, and catalogs from any Access table or query. There's no VBA involved in any of that. Just learn to use Word's Mail Merge feature, and you're on your way.

Yet a third approach would be to create a general Word template that contains bookmarks (placeholders) for data to be filled in later. Then, use VBA in Access to replace the bookmarks with data from an Access table. This is particularly handy when you want to be able to click a button on a form in Access to print one Microsoft Word form letter.

Creating the Word template

The first step to merging data from Access into a Word document is to create a Word document template (DOT file). Start with any blank Word document and type your form letter (or whatever you wish) as you normally would in Word. You can use any and all Word features — fonts, pictures, tables, WordArt, whatever.

Wherever you want VBA to insert data from an Access table, create a Word bookmark. A bookmark (in Word) is just a placeholder. Bookmarks are usually hidden, so before you add any bookmarks to the document, choose ToolsO Options from the Word menu bar. In Word's Options dialog box that opens, click the View tab, select Bookmarks, and then click OK.

You can insert bookmarks however you wish. Here's how I usually do it:

1. Move the cursor to where you want VBA to insert data from Access.

2. Type a short, simple name for the bookmark.

The name cannot contain spaces nor punctuation, and it must start with a letter.

3. Select (double-click) the name you just typed and then press Ctrl+C to copy it.

4. Choose InsertOBookmark from the Word menu bar.

5. Press Ctrl+V to paste the typed name as the bookmark name.

6. Click the Add button.

You'll need to go through those steps for each item of data that you want VBA to insert later. In the example shown in Figure 14-7, I add three bookmarks to the document. Note that the square brackets around each bookmark's name are visible because the Show Bookmarks option is on. I didn't type any of those square brackets.

The bookmark names in the sample document template get data from Access and VBA as follows:

1 TodaysDate: VBA will replace this bookmark with the current date.

1 AddressLines: VBA will replace this line with as many lines as necessary to show the recipient's address.

1 Salutation: VBA will replace this with the customer's first name or just Sirs if the first name is Null (empty) in the record being printed.

When you finish typing your document and all your bookmarks are in place, follow these steps to save the document as a Word template:

1. Choose FileOSave As from Word's menu bar.

2. From the Save As Type option at the bottom of the Save As dialog box, choose Document Template (*.dot).

3. Use the Save In drop-down list to navigate to the folder in which you want to store the document template.

Your best bet would be to put it in the same folder as your database MDB file, but you can use any folder you wish — provided that you know the full path to the document. For example, if you put the Word template in your Shared Documents folder, the path to that document is C:\Documents and Settings\All Users\Documents.

4. Name the document (but don't change the .dot extension) and click the Save button.

I name my document template WordFormLetter.dot, but you can name yours however you wish.

5. Close Microsoft Word.

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

Bookmarks

Figure 14-7:

Word document template with bookmarks to later accept Access data.

WordFormLetter.dot (Read

Only) - Microsoft Word

File Edit View insert

Normal + Arial I t Arial Narrow

(format lools Table Window Help Type a question for help tsfflnraii^Elir - © _

- 12 - B I D m m m ■ t= - =E :E it □ - S - A - .

File Edit View insert

Normal + Arial I t Arial Narrow

(format lools Table Window Help Type a question for help tsfflnraii^Elir - © _

- 12 - B I D m m m ■ t= - =E :E it □ - S - A - .

1234-Arbitrary Highway Randomville, CA 98765

TodaysDate -

AddressUneE

Dear Salutation —

The main body of th Each Item In the squ shown inside the bre Customers table.

e letter starts here, and can contain any text, fonts, and pictures you want, are brackets above is a bookmark, defined in this document with the name ckets. Access will replace each bookmark with data from a record in its

At 1,4" Ln 5 Col 11 PEC TRK EXT OVR English (U,5 Q3C

At 1,4" Ln 5 Col 11 PEC TRK EXT OVR English (U,5 Q3C

Creating the Access form

For this example, I create a simple form that's bound to a table named Customers, with the usual name and address fields that you'd 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'll see them referred to with square brackets — [FirstName], [LastName], [Company], and so forth. The e-mail address control, near the bottom, isn't really relevant to the topic at hand. The dropdown 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.

Excel 2007 User Forms Vba

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, I 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: Merge Code

Private Sub MergeBttn_Click()

'Declare variables for storing strings (text). Dim AddyLineVar, 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 "Sirs". SalutationVar = "Sirs" 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 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.dot"

'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 will execute any time 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 will become 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, SalutationVar As String

In the Customers table I 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, Sirs:.

'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 "Sirs". SalutationVar = "Sirs"

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/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 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 won't accept the statements that follow if you haven't already selected the Microsoft Word 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 my example, I place the Word document in the same folder as the database MDB file (which makes it easy to find). In Access, you can use Application. CurrentProject.Path to get the path to the currently open database. I name my Word document WordFormLetter.dot. 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.dot"

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

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

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'll probably want to make Word visible so 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 the bookmarks in the document.

The following 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) will be 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 .ItemibookmarkName) 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("Addressl_ines").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, I 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

Onscreen, 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. (There's no need to save the letter after it's printed because you'll 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 I 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 actually print the letter, so you'd never actually see the form on top of the Word document. (I had to superimpose the form there.) However, you would get a printed copy of the letter shown in the figure.

Figure 14-9:

Record from the form merged into a form letter.

Figure 14-9 shows an example of using the procedure. There I 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 actually print the letter, so you'd never actually see the form on top of the Word document. (I had to superimpose the form there.) However, you would get a printed copy of the letter shown in the figure.

+1 -3

Responses

  • LUKAS
    How to insert contents in table in word using vba?
    8 years ago
  • teodata
    How to invisible the button in vba of word file?
    7 years ago
  • Adelmo Siciliano
    How to insert data into word bookmark using vba?
    7 years ago

Post a comment