Automating Word using Access

Automating Word using Access

As with most Microsoft Office applications, Word opens itself up to automation from Access. There are several reasons why you might want to integrate Word into your application:

If you are creating data reports that are to be sent to other people, they might be more comfortable with it in Word.
You can use a Word document as a 'template', giving the user more flexibilty with the layout of the documentation produced from your application.
The user might want to create various letters in bulk, but have the ability to manually edit some of them as required.

In the example below, I show how to use Word Automation to loop through the Employees table in the Northwind sample database, and output a letter to each employee, using a previously created Word document as the body of the letter.

Private Const WD_DONOTSAVECHANGES = 0
Private Const WD_ALIGN_PARAGRAPH_RIGHT = 2
Private Const WD_ALIGN_PARAGRAPH_LEFT = 0
Private Const WD_COLLAPSE_END = 0

Public Sub sAutoWord()
'   Procedure to demonstrate the automation of MS Word from MS Access
'   Notes:
'       This example uses late binding, so that it is less important as to the version of Word
'       that is deployed on the target computer.
    On Error GoTo E_Handle
    Dim db As Database
    Dim rsEmployee As Recordset
    Dim strCurrentPath As String
    Dim strTemplateFile As String
    Dim objWord As Object
    Dim objWordDoc As Object
    Dim objWordRange As Object
    Set db = CurrentDb
    Set rsEmployee = db.OpenRecordset("Employees")
    Set objWord = CreateObject("Word.Application")
    strCurrentPath = Left(CurrentDb.Name, Len(CurrentDb.Name) - Len(Dir(CurrentDb.Name)))
    strTemplateFile = "C:\Template.doc"
    If Not (rsEmployee.BOF And rsEmployee.EOF) Then
        Do
            Set objWordDoc = objWord.Documents.Add
            With objWordDoc
                .Content.InsertFile strTemplateFile
                .Content.InsertBefore "Dear " & rsEmployee!FirstName & vbCrLf & vbCrLf
                .Content.InsertBefore rsEmployee!Country & vbCrLf & vbCrLf
                .Content.InsertBefore rsEmployee!PostalCode & vbCrLf
                If Not IsNull(rsEmployee!region) Then .Content.InsertBefore rsEmployee!region & vbCrLf
                .Content.InsertBefore rsEmployee!City & vbCrLf
                .Content.InsertBefore rsEmployee!Address & vbCrLf
                .Content.InsertBefore rsEmployee!Titleofcourtesy & (" " + Left(rsEmployee!FirstName, 1)) & (" " + rsEmployee!LastName) & vbCrLf
                .Content.InsertBefore Format(Date, "d mmmm, yyyy") & vbCrLf & vbCrLf
                .Paragraphs(1).Alignment = WD_ALIGN_PARAGRAPH_RIGHT
                .Content.InsertBefore vbCrLf & vbCrLf & vbCrLf & vbCrLf & vbCrLf
                .SaveAs FileName:=strCurrentPath & rsEmployee!employeeid & ".doc"
                .Close (WD_DONOTSAVECHANGES)
            End With
            rsEmployee.MoveNext
        Loop Until rsEmployee.EOF
    End If
sExit:
    On Error Resume Next
    rsEmployee.Close
    Set rsEmployee = Nothing
    Set db = Nothing
    Set objWordDoc = Nothing
    objWord.Quit
    Set objWord = Nothing
    Exit Sub
E_Handle:
    MsgBox Err.Description & vbCrLf & "sAutoWord", vbOKOnly + vbCritical, "Error: " & Err.Number
    Resume sExit
End Sub

The other option to using Automation such as this is to create the Word Document with Bookmarks, and then do a mail merge, but this requires the user to know how to work with Bookmarks in Word!!

Top

Last modified at 06/06/2006 14:55:45