ACC: Sending the Current Record to Word 97 with Automation

• Microsoft Access versions 7.0, 97
• Microsoft Word 97

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article describes how to merge the current record in a Microsoft Access 7.0 and 97 object into a Microsoft Word 97 document, print it, and then close Microsoft Word 97.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

MORE INFORMATION

Microsoft Word 97 uses the Visual Basic for Applications programming model instead of the WordBasic flat command model used in earlier versions.

For information about how to send the current record to Microsoft Word version 7.0 or earlier using WordBasic, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q124862
   TITLE     : ACC: Sending the Current Record to Word with OLE Automation

Creating a Microsoft Word 97 Document

1. Start Microsoft Word 97 and create the following new document:

         First Last
         Address
         City, Region, PostalCode
   

         Dear Greeting,
   

         Northwind Traders would like to thank you for
         your employment during the past year. Below
         you will find your photo. If this is not your
         most current picture, please let us know.
   

         Photo
   

         Sincerely,
   

         Northwind Traders
   

2. Create a bookmark in Microsoft Word 97 for the words "First," "Last," "Address," "City," "Region," "PostalCode," "Greeting," and "Photo":

       a. Select the word "First."
   

       b. On the Insert menu, click Bookmark.
   

       c. In the Bookmark Name box, type "First," (without the quotation
          marks) and then click Add.
   

       d. Repeat steps 2a-2c for each of the remaining words, substituting
          that word for the word "First" in steps 2a and 2c.
   

3. Save the document as C:\My Documents\MyMerge.doc, and then quit Microsoft Word 97.

Sending Data to Microsoft Word from a Microsoft Access Form

1. Start Microsoft Access and open the sample database Northwind.mdb.

2. Set a reference to the Microsoft Word 8.0 Object Library. To do so, follow these steps:

       a. Open any module in Design view.
   

       b. On the Tools menu, click References.
   

       c. Click Microsoft Word 8.0 Object Library in the Available References
          box. If that selection does not appear, browse for Msword8.olb,
          which installs by default in the C:\Program Files\Microsoft
          Office\Office folder.
   

       d. Click OK.
   

       e. Close the module.
   

3. Open the Employees form in Design view.

4. Add a command button to the form and set the following properties:

         Command Button:
            Name: MergeButton
            Caption: Send to Word
            OnClick: [Event Procedure]
   
   

5. Set the OnClick property of the command button to the following event procedure.

   NOTE: In the following sample code, you must remove the comment from one line of code as indicated, depending on your version of Microsoft Access.

         Private Sub MergeButton_Click()
         On Error GoTo MergeButton_Err
            Dim objWord As Word.Application
            ' Copy the Photo control on the Employees form.
            DoCmd.GoToControl "Photo"
            ' Remove the following comment in Microsoft Access 97.
            ' DoCmd.RunCommand acCmdCopy
            ' Remove the following comment in Microsoft Access 7.0.
            ' DoCmd.DoMenuItem acFormBar, acEditMenu, acCopy, , acMenuVer70
            ' Start Microsoft Word 97.
            Set objWord = CreateObject("Word.Application")
            With objWord
               ' Make the application visible.
               .Visible = True
               ' Open the document.
               .Documents.Open ("c:\my documents\mymerge.doc")
               ' Move to each bookmark and insert text from the form.
               .ActiveDocument.Bookmarks("First").Select
               .Selection.Text = (CStr(Forms!Employees!FirstName))
               .ActiveDocument.Bookmarks("Last").Select
               .Selection.Text = (CStr(Forms!Employees!LastName))
               .ActiveDocument.Bookmarks("Address").Select
               .Selection.Text = (CStr(Forms!Employees!Address))
               .ActiveDocument.Bookmarks("City").Select
               .Selection.Text = (CStr(Forms!Employees!City))
               .ActiveDocument.Bookmarks("Region").Select
               .Selection.Text = (CStr(Forms!Employees!Region))
               .ActiveDocument.Bookmarks("PostalCode").Select
               .Selection.Text = (CStr(Forms!Employees!PostalCode))
               .ActiveDocument.Bookmarks("Greeting").Select
               .Selection.Text = (CStr(Forms!Employees!FirstName))
               ' Paste the photo.
               .ActiveDocument.Bookmarks("Photo").Select
               .Selection.Paste
            End With
            ' Print the document in the foreground so Microsoft Word 97
            ' will not close until the document finishes printing.
            objWord.ActiveDocument.PrintOut Background:=False
            ' Close the document without saving changes.
            objWord.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
            ' Quit Microsoft Word 97 and release the object variable.
            objWord.Quit
            Set objWord = Nothing
            Exit Sub
         MergeButton_Err:
            ' If a field on the form is empty
            ' remove the bookmark text and continue.
            If Err.Number = 94 Then
               objWord.Selection.Text = ""
               Resume Next
            ' If the Photo field is empty.
            ElseIf Err.Number = 2046 Then
               MsgBox "Please add a photo to this record and try again."
            Else
               MsgBox Err.Number & vbCr & Err.Description
            End If
            Exit Sub
         End Sub 
   
   

6. Save the Employees form and open it in Form view.

7. Click the Send To Word button to start Microsoft Word 97, merge data from the current record on the form into MyMerge.doc, print the document, and then close Microsoft Word 97.

  .ActiveDocument.Bookmarks("Last").Select
  .Selection.Text = (CStr(Forms!Employees!LastName))
  'add this line to reapply the bookmark name to the selection
  .ActiveDocument.Bookmarks.Add Name:="Last",Range:=Selection.Range

REFERENCES

For more information about Automation between Microsoft Access 97 and Microsoft Word, search the Help Index for "sharing data between applications, Microsoft Word mail merge data."

For more information about Automation between Microsoft Access 7.0 and Microsoft Word, search the Help Index for "Word (Microsoft), sharing data."

For more information about bookmarks, search the Microsoft Word 97 Help Index for "bookmarks," or ask the Microsoft Word 97 Office Assistant.

Keywords          : kbmacro kbole kbprg word8 word95 IntpOleA OffVBA kbfaq
Version           : 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto