Changes in functionality mean changes in the Word object model. Word gained a number of new properties and events for the mail merge interface. And the change in the default connection method will break many applications that link in data sources for mail merge.
Up until Word 2002, there were basically two ways you could link in a data source with VBA for Excel and Access: DDE and ODBC. You could distinguish between the two with a glance at the OpenDataSource code: if you saw the term "DSN" in the Connection parameter, you knew it was ODBC. If you saw a file name in the Name parameter, it was almost certainly a DDE connection.
Other database formats were almost certainly ODBC connections (except, perhaps, for text files).
With the move to OLEDB as the default, this all changed. The OLEDB connection method also uses the Name parameter, so recorded VBA code for making DDE connections to Excel and Access will often fail, because Word 2002 tries to set up an OLEDB connection, and can't.
In order to convert a Word 97 or 2000 macro that uses OpenDataSource to Word 2002, add the SubType:=wdMergeSubTypeWord2000 parameter at the end. Example:
OpenDataSource Name:="C:\MyDB.mdb", _
SQLStatement:="SELECT * FROM [MyTable]", _
SubType:=wdMergeSubTypeWord2000
ODBC connections should continue to work as previously.
More information on automating mail merge connections can be found in the Knowedge Base article HOWTO: Automate Word 2002 with VB to Create a Mail Merge [285332]
The ability to search records didn't work correctly in Word 97 or Word 2000, but was finally fixed in the user interface in Word 2002. The new VBA FindRecord method, however, simply doesn't execute. If you want to use Find over VBA, you have to resort to the old, Word 2000 command - with it's described problem and workaround. The sample macro uses the old FindRecord2000 method to search for the desired record. If it is found, the function returns True. Due to the problem behavior of this method, it's now necessary to search for something that cannot return a result. The ANSI character 7 is used by Word for the end of a table cell; since this can't be part of a merge field's content, it's safe to assume that a search for this character will never be successful, thus resetting the user interface so that the mail merge can proceed.
Function FindRec2002(mm As Word.MailMerge, _
szText As String, szField As String) _
As Boolean
Dim lRec As Long
lRec = mm.DataSource.ActiveRecord
mm.DataSource.ActiveRecord = _
wdFirstRecord
FindRec2002 = _
mm.DataSource.FindRecord2000( _
FindText:=szText, Field:=szField)
If FindRec2002 Then
mm.DataSource.FindRecord2000 _
FindText:=Chr$(7), Field:=szFeld
Else
mm.DataSource.ActiveRecord = lRec
End If
End Function
From a developer's point of view, this may be THE greatest new functionality in Word 2002. You can now intercept the beginning of the merge process (but ONLY if the merge is executed from the task pane, which limits rather the usefulness), you can perform actions before each record is merged, after each record is merged, and at the end of the merge process.
This gives you the opportunity to do all kinds of great things, such as processing data from each record before it's merged, or running a spell check on the merge result document.
This a zipped sample file that shows you how to set up a macro project to use the events. It has an AutoOpen macro that links to the sample data file located in the same folder, and initializes the events. Click the toolbar button to execute the merge to a new document. The MailMergeBeforeRecordMerge event will abbreviate the content of the FirstName field to a single character (an initial) for each record.
Example of other frequent requests such an approach can help fulfill:
There is also a VBA interface for customizing the Mail merge Wizard taskpane. The ShowWizard method lets you
With the MailMergeWizardStateChange event, you can validate the main merge document and perform other actions everytime the user moves between steps. It's even possible to prevent the user moving to another step.
You can also add your own, custom entry to Step 6 with the ShowSendToCustom property, so that the merge is executed to something other than the standard printer, fax, email or new document targets. It fires the MailMergeWizardSendToCustom event.
Top of Page Close window