March 2, 2011, 10:45 a.m.
posted by osi
Manage Word Documents from Access
Tap into the Word object library to copy Access data directly into a Word 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.
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. Note that your version number of the Word library might differ, so use whatever you have.
Setting a reference to the Word object library
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 recset = New ADODB.Recordset Dim row_num As Integer Dim col_num As Integer Dim word_doc As Object 'Assumes Word doc is in same path - change name and path as needed 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 With word_doc 'navigate to Word bookmark and create 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, _ NumRows:=recset.RecordCount, NumColumns:=recset.Fields.Count For row_num = 1 To recset.RecordCount For col_num = 1 To recset.Fields.Count .Tables(.Tables.Count).Cell(row_num, col_num). Select Selection.TypeText recset.Fields(col_num - 1) Next col_num 'next database record recset.MoveNext Next row_num End With recset.Close Set recset = Nothing Set word_doc = Nothing MsgBox "done" End Sub
Here are some highlights of this code:
The Access data is gathered into a recordset.
The GetObject function is referenced to the existing Word document. Note that this example assumes the database and the document are in the same directory. Also, the name of the document is hardcoded, 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 table is populated cell by cell by looping through the recordset. An outer loop cycles through the recordset rows, and in each row an inner loop cycles through each field.
The Data Has Landed Intact
After running this code, the document has a table with the data, as shown in Figure. Note that there is no connection back to Access; the data is just essentially part of the Word document.
Note that this simplistic example assumes a number of things: the bookmark exists, there is no existing table, and the Access table isn't too large in terms of rows and fields to make the Word table too densely packed.
Nonetheless, this hack serves as a brief introduction to tapping into Word objects. Because the reference has been set to the library, you can now use the Object Browser in Access to review Word's objects.