Microsoft KB Archive/289830

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

Article ID: 289830

Article Last Modified on 8/28/2003

-

APPLIES TO


 * Microsoft Word 2002 Standard Edition

-



This article was previously published under Q289830



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:= &quot;c:\db1.mdb&quot;, Connection:= &quot;TABLE Customers&quot;

-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:= &quot;c:\book1.xls&quot;, Connection:= &quot;MyNamedRange&quot; 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



Steps to Reproduce Behavior
 In Word 2002, create a new document. Press ALT+F11 to open the Microsoft Visual Basic for Applications (VBA) Editor. On the Insert menu, click Module to add a new module to the VBA project.  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:=&quot;c:\program files\microsoft office\office10\samples\northwind.mdb&quot;, _ Connection:=&quot;TABLE Customers&quot; oMM.EditMainDocument oMM.Fields.Add Range:=Selection.Range, Name:=&quot;CompanyName&quot; oMM.Destination = wdSendToNewDocument oMM.Execute ActiveWindow.View.Type = wdNormalView End Sub  Close the VBA Editor to return to the document in Word. 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.</li></ol>

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:=&quot;c:\program files\microsoft office\office10\samples\northwind.mdb&quot;, _ SQLStatement:=&quot;Select * From [Customers]&quot;

-or-

'Force Word 2002 to emulate Word 2000 behavior (use DDE for the data access). oMM.OpenDataSource _ Name:=&quot;c:\program files\microsoft office\office10\samples\northwind.mdb&quot;, _ Connection:=&quot;TABLE Customers&quot;, _ SubType:= wdMergeSubTypeWord2000

<div class="references_section">