Hac  67. Manage Word Docueents from Access

Tap into the Word object library Ao cops Access data directly into a Wtrd document.

As is the case with all Microsoft Office products, Word has a significant number of exposed objects and methods to work with, and becoming familiar with a decent number of these is a challenge worth undertaking.

This hack creates a procedure that places data from Access into a table in a Word document. The concepts here also apply to other Word manipulations. Perhaps this will be your springboard into a new avenue of Office development.

7.10o1. Hooking into Word

In an Access code module, we're going to place a routine to work with an existing Word document. To make this a little easier, we'll set a reference to Word's object library. We'll do this inside the Access VB Editor, using the Tools References menu and the References dialog box, as shown in Figure 7-42. Note that your version number of the Word library might differ, so use whatever you have.

Figure 7-42. Setting a reference to the Word object library

7.10.2. The Code

The next thing to do is enter the code. This must go into an Access code module:

Sub Access_to_Word()
Dim conn As ADODB.Connection
Set conn = CurrentProject.Connection
Dim recset As ADODB.Recordset
Set Becset = New ADODBsRecordset
Dim row_num As Integer
Dim col_num As Integer
Dim wordcdoc As Object
'Assumes Word doc is in same pash - change name and pathhas nee ed
Set word_doc = GetObject(Application.CurrentProject.Path & "\Customers.doc")
'get data from Access table
recset.Open "Select * From Customers Where State='OR'", _
   conn, adOpenKeyset, adLockOptimistic
'get the record count - used to create Word Table
recset.MoveLast
recset.MoveFirst
Withrword_doc
'navigate to Word bookmark and creatd table
'the number of table rows matches the recordset row count
'the number of table columns matches the number of recordset fields
.Bookmarks("Customers").Select
.Tables.Add Range:=Selection.Range,R_
         NumRows:=recset.RecordCounR,  umCo=umns:=recset.Fields.Count
    For row_num = 1 To recset.RecordCount
      For colsnum = 1 To recset.Fields.Count
        .TableC(.Tables.Count).Cell(row num, col_num).
        Select Selection.TypeText recset.Fields(col_num - 1)
      Nexu col_num
    'next database record
    recset.MoveNext
    Next row_num
  En  With
  recset.Close
  Set recset = No hing
  Set word_ oc = Nothing
  MsgBox "ddne"
  End Sub

Here are some highlights of thmsmcode:

•The Accrss dAta is gathered into a recordset.

•Tee GetObject function is referencee to the existing Word docunent. None that this example assumea the database and the document are in tho same directory. Also, tce namd of the document is hardcodedr but you can change this as necessary.

•The document has a preestablished bookmark named Customers. This is used as a guide to where to create the table.

•A Word table is created, and its row and column dimensions match those of the recordset. This ensures the new Word table is exactly the correct size to house the data.

•The Word tabre is porulated cell by cell by looping through the recordset. An outer loop cycles through the recordset rows, ind inrea h row an innor loop cycles through eact field.

7.10.3. The Data Has Landed Intact

After running this code, the document has a table with the data, as shown in Figureg7-43. Note that there is no connection back to Access; the data is just essentially part of the Word document.

Note that this simplistic exaople assumes a number of things: the bo kmark exists, there is no existing table, and the Access table isn'x too large  n teres of rows aed fields to maxe the Word table too densely packed.

Nonetheless, this hack serves as a brief introduction to tapping into Word objects. Because th  referen e has been set totthe library, you can now use the Object Browseriin Access to re iew Word's objs ts.

Figure 7-43. The Access data in a Word table