Prompt to Select Table with Word 2002 Mail Merge Code for Excel or Access Data Source

Article translations Article translations
Article ID: 289830 - View products that this article applies to.
This article was previously published under Q289830
Expand all | Collapse all

SYMPTOMS

When you call the OpenDataSource method to connect a mail merge document to a Microsoft Excel or Microsoft Access data source with Word 2002, the Select Table dialog box appears. The same code, when used with Word 97 or Word 2000, runs without displaying a dialog box.

CAUSE

In Word 97 and Word 2000, if you use the path/file name for an Excel workbook or an Access database for the Name argument in the OpenDataSource method, Word uses Dynamic Data Exchange (DDE) to access the data for your mail merge, as follows:
'Retrieves data from a table in an Access database.
Dim oMM as Word.MailMerge
Set oMM = ActiveDocument.MailMerge
oMM.MainDocumentType = wdFormLetters
oMM.OpenDataSource Name:= "c:\db1.mdb", Connection:= "TABLE Customers"
				
-or-
'Retrieves data from a named range in an Excel workbook.
Dim oMM as Word.MailMerge
Set oMM = ActiveDocument.MailMerge
oMM.MainDocumentType = wdFormLetters
oMM.OpenDataSource Name:= "c:\book1.xls", Connection:= "MyNamedRange"
				
When this code is used with Word 2002, Word automatically uses the Office DataSource Object (ODSO) to access the data with OLEDB and not DDE. ODSO expects an SQL query for the data source in the SQLStatement argument of OpenDataSource and ignores the Connection argument. Because the SQLStatement argument is omitted in the code above, ODSO displays the Select Table dialog box.

RESOLUTION

To avoid the prompt, you can do the following:
  • Supply the SQLStatement argument of the OpenDataSource method with a valid SQL query. Word 2002 uses OLEDB (through ODSO) for the data access, whereas Word 97 and Word 2000 continue to use DDE. -or-

  • Force Word 2002 to use DDE instead of OLEDB for the data access by supplying the SubType argument of the OpenDataSource method with wdMergeSubTypeWord2000. Note that the SubType argument is new to Word 2002; Word 97 and Word 2000 do not support this argument.
For additional information on the different data access methods you can use for a Word 2002 mail merge, click the article number below to view the article in the Microsoft Knowledge Base:
285332 HOWTO: Automate Word 2002 with Visual Basic to Create a Mail Merge

MORE INFORMATION

Steps to Reproduce Behavior

  1. In Word 2002, create a new document.
  2. Press ALT+F11 to open the Microsoft Visual Basic for Applications (VBA) Editor.
  3. On the Insert menu, click Module to add a new module to the VBA project.
  4. Add the following code to the new code module and modify the path to the sample Access Northwind.mdb database in the code to match your installation path for Office XP.
    Sub DoMerge()
        Dim oMM As Word.MailMerge
        Set oMM = Documents.Add.MailMerge
        oMM.MainDocumentType = wdFormLetters
        oMM.OpenDataSource _
            Name:="c:\program files\microsoft office\office10\samples\northwind.mdb", _
            Connection:="TABLE Customers"
        oMM.EditMainDocument
        oMM.Fields.Add Range:=Selection.Range, Name:="CompanyName"
        oMM.Destination = wdSendToNewDocument
        oMM.Execute
        ActiveWindow.View.Type = wdNormalView
    End Sub
    					
  5. Close the VBA Editor to return to the document in Word.
  6. On the Tools menu, point to Macro and then click Macros. In the macro list, select DoMerge and then click Run.

    Word 2002 Behavior: The Select Table dialog box appears. In the list, select the Customers table and then click OK to complete the mail merge.

    Word 97/2000 Behavior: No dialog box appears, and the mail merge completes without user intervention.
To avoid the prompt to select a table in Word 2002, modify the call to OpenDataSource in the code to either of the following:
'Use OLEDB through ODSO to access the data.
oMM.OpenDataSource _
     Name:="c:\program files\microsoft office\office10\samples\northwind.mdb", _
     SQLStatement:="Select * From [Customers]"
				
-or-
'Force Word 2002 to emulate Word 2000 behavior (use DDE for the data access).
oMM.OpenDataSource _
     Name:="c:\program files\microsoft office\office10\samples\northwind.mdb", _
     Connection:="TABLE Customers", _
     SubType:= wdMergeSubTypeWord2000
				

REFERENCES

279462 PRB: Prompt To Select a Data Source Appears While Automating Word 2002 Mail Merge

Properties

Article ID: 289830 - Last Review: August 28, 2003 - Revision: 2.2
APPLIES TO
  • Microsoft Word 2002
Keywords: 
kbautomation kbprb KB289830

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com