Working with Word from Access

The first sample in this section demonstrates how to build, design, and populate a table in Word based on data in an Access table. It uses Automation to control Word from Access, and it even includes a simple Access form for invoking the Automation procedure. The second and third samples tackle two mail merge tasks programmatically: generating mailing labels and generating a form letter.

The form letter sample in this chapter facilitates mailing a form letter to a list of recipients, which is the same task tackled in Chapter 6 by other means. You can compare the two approaches to see which best fits your needs. In general, the philosophy behind having multiple components is that you should use each to do what it does best. The sample in this chapter enables Access to store data and Word to generate form letters for printing. If you find it easy to work with the Word object model and the MailMerge object, the approach demonstrated in this chapter offers opportunities to expand your skills in pursuit of automating common business requirements. If you prefer to concentrate on becoming expert in Access by itself, the approach in Chapter 6 is more suitable for you.

Automating Word from Access

The following Automation sample transfers contents from a recordset based on a table in Access to a table in Word. When you have references to multiple object models with similar terms, you should include a prefix before the object data type designation in its declaration ”you should use Word.Table instead of Table , for example. Recall that the ADOX library supports a Table class as well. Specifying Word.Table tells the VBA interpreter which kind of Table object you want. Access can also declare a Table object from the ADOX library. Also, note that the Range object in Word behaves differently than the one in Excel. VBA and IntelliSense facilitate building cross-application solutions, but they do not relieve you from learning the object models for individual applications.

 SubfromAccessToWordTable()  DimmyWDAppAsWord.Application DimmyRangeAsWord.Range,myTableAsWord.Table DimacellAsWord.Cell,emailColAsInteger Dimrst1AsNewRecordset,irowAsInteger     'Openthetablewiththenewcontacts Withrst1 .ActiveConnection=CurrentProject.Connection .Open "oe4pab",,adOpenKeyset,adLockOptimistic,adCmdTable EndWith     'CreateaWordapplicationinstanceandturnon 'theAssistant'sworkinganimation AssistantWorkingOn SetmyWDApp=CreateObject("Word.Application")     'Addadocumenttotheapplicationandatabletothedocument. 'Specifyrowstoequalonemorethannumberofrowsine-mail 'addresstableinAccess. myWDApp.Documents.Add SetmyRange=myWDApp.ActiveDocument.Range(0,0) myWDApp.ActiveDocument.Tables.AddRange:=myRange,_ NumRows:=rst1.RecordCount+1,NumColumns:=3     'Insertcolumnheadingsfortable WithmyWDApp.ActiveDocument.Tables(1).Rows(1) .Cells(1).Range.Text=rst1.Fields(0).Name .Cells(2).Range.Text=rst1.Fields(1).Name .Cells(3).Range.Text=rst1.Fields(2).Name EndWith     'Insertfirstname,lastname,ande-mailfromAccesstable. 'Insertcontactinformationinthesecondthroughthelastrow. Forirow=2TomyWDApp.ActiveDocument.Tables(1).Rows.Count emailCol=0 ForEachacellIn_ myWDApp.ActiveDocument.Tables(1).Rows(irow).Cells acell.Range.Text=IIf(IsNull(rst1.Fields(emailCol)),_  "",rst1.Fields(emailCol)) emailCol=emailCol+1 Nextacell rst1.MoveNext Nextirow     'Formattabletofitcontent,turnonidleanimation,and 'makeWordvisiblesothatusercanseetable in Word   myWDApp.ActiveDocument.Tables(1).AutoFitBehaviorwdAutoFitContent AssistantIdleOn myWDApp.Visible=True     EndSub 

The procedure starts by opening a Recordset object based on a table in Access ”the familiar oe4pab table that you saw in the Outlook samples in "Working with Outlook from Access" in this chapter. Because the application needs the RecordCount property, the sample code uses the adOpenKeyset constant for the cursor type specification. After opening the recordset, the application turns on the working animation for the Assistant and runs the CreateObject function to create a fresh instance of Word.

The procedure then constructs the table in Word. It adds a new document and then adds a table to the document's top left corner. The parameters of the Add method for the Table object specify that the table will have one more row than there are rows in the Access table. This allows one row of column headers plus all the data in the oe4pab table. Before starting to work with the recordset values, the procedure writes the column headers in the first row. These are the field names for the table in Access.

A pair of nested For loops navigate through the cells in the table. The outer loop progresses sequentially through the rows. The inner one marches across the columns within a row. Notice that Word has a Cells collection for the columns within the row of a table. The inner loop navigates to individual cells within a row of a Word table. The reference to a cell starts with the Automation object, myWDApp , and then hierarchically moves to the ActiveDocument object, the first table on the document, and the row in the table. After identifying a cell within a row to process, an IIf function based on the recordset values computes a value for the Text property of the cell. The emailcol integer value coordinates with the rst1 variable to deposit recordset column values into the columns for the Word table.

After iterating through all of the table's cells, the procedure closes by performing three steps. First it reformats the columns' widths so that they're wide enough to display column values without wrapping. Then, it assigns an idle animation to the Assistant. This essentially turns off the working animation that starts just before the Word Application object launches. Finally the procedure sets the Application object's Visible property to True . This changes the focus from Access to Word.

Figure 9-7 shows the frmAutomationSample Access form in the Chapter09.mdb file that lets users start the fromAccessToWordTable procedure by clicking a button. The code behind the form merely invokes the fromAccessToWordTable procedure for the button's click event. After the event procedure concludes, Word opens with a table of values from Access.

Figure 9.7: This form invokes the fromAccessToWordTable procedure.

Producing Mailing Labels

The built-in Word mail merge feature can draw on an Access database table or query as a record source for generating mailing labels. While you can programmatically lay out the mail merge fields or bookmarks to support the placement of Access data on Word documents, it's often much simpler to lay them out manually where yo