MailMerge macros running very slowly

[Steve Forrow]

When I use the MailMerge Helper to perform a merge to
produce a single document the time taken for the merge is
less than 1 second. However if I record that process as a
macro and run the macro it takes nearly a minute.

Does nayone know why?

The main document is in rtf format, the source data is a
text file with 800 fields, the machine is a pentium III
with 128Mb of RAM.

Versions:
Word2002 (10.4219.4219) SP2
WindowsXP Professional version 5.1 (Build
2600.xpsp.1.020928-1920:service pack 1)

 

[Cindy M -WordMVP-]

Hi Steve,

Copy/paste the code the macro recorder produced into a reply,
here, please?

When I use the MailMerge Helper to perform a merge to
produce a single document the time taken for the merge is
less than 1 second. However if I record that process as a
macro and run the macro it takes nearly a minute.

Does nayone know why?

The main document is in rtf format, the source data is a
text file with 800 fields, the machine is a pentium III
with 128Mb of RAM.

Versions:
Word2002 (10.4219.4219) SP2
WindowsXP Professional version 5.1 (Build
2600.xpsp.1.020928-1920:service pack 1)

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Sep
30 2003)
http://www.mvps.org/word

This reply is posted in the Newsgroup; please post any follow
question or reply in the newsgroup and not by e-mail

 

[Steve]

Cindy

As requested, here is the macro that was recorded, if you
step through using the debugger each command is processed
ok until the final ".execute", that takes about 60 seconds
to complete.

Regards

Steve

Sub MailMergeXP()
Attribute MailMergeXP.VB_Description = "Macro recorded
11/11/2003 by tissem80"
Attribute MailMergeXP.VB_ProcData.VB_Invoke_Func
= "Normal.NewMacros.MailMergeXP"
'
' MailMergeXP Macro
' Macro recorded 11/11/2003 by tissem80
'
Documents.Open FileName:="C:\ACON\TEMP\1009837_t.rtf",
ConfirmConversions:=False, _
ReadOnly:=False, AddToRecentFiles:=False,
PasswordDocument:="", _
PasswordTemplate:="", Revert:=False,
WritePasswordDocument:="", _
WritePasswordTemplate:="", Format:=wdOpenFormatAuto
ActiveDocument.MailMerge
.MainDocumentType := wdFormLetters
OpenDataSource
Name:="C:\Acon\Temp\1009837_data.txt",
ConfirmConversions:=False, ReadOnly:= _
False, LinkToSource:=True,
AddToRecentFiles:=False, PasswordDocument:="", _
PasswordTemplate:="", WritePasswordDocument:="",
WritePasswordTemplate:= _
"", Revert:=False, Format:=wdOpenFormatAuto,
Connection:="", SQLStatement _
:="", SQLStatement1:="",
SubType:=wdMergeSubTypeOther

.Destination = wdSendToNewDocument
.MailAsAttachment = False
.MailAddressFieldName = ""
.MailSubject = ""
.SuppressBlankLines = True
With .DataSource
.FirstRecord = 1
.LastRecord = 1
End With
.Execute
End With
End Sub

 

[Cindy M -WordMVP-]

Hi Steve,

As requested, here is the macro that was recorded, if you
step through using the debugger each command is processed
ok until the final ".execute", that takes about 60 seconds
to complete.

OK, here's some background for you:

1. The mail merge helper will use either ODBC or Word's
internal text file converter for text files. From the code
you post, I'm guessing it's the latter.

2. Word 2002's interface will, by default, use OLEDB, which
is notoriously slower.

3. It's very difficult to coerce Word 2002 to use the file
converter via VBA. The recorded code will be interpreted as
"OLEDB" by Word

4. You might try substituting wdMergeSubTypeWord2000 for
wdMergeSubTypeOther in the code you have and see if that
makes a difference

5. If not, then I'd see if ODBC gives you any better result.
Activate the "Confirm conversions on open" checkbox in
Tools/Options/General. Now use the Word 2002 interface to
select the text file. You should get a list of connection
methods, and ODBC will probably NOT be included. So activate
"Show all" and look for the text ODBC driver. Select it and
test (also record it in a macro and test).

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Sep
30 2003)
http://www.mvps.org/word

This reply is posted in the Newsgroup; please post any follow
question or reply in the newsgroup and not by e-mail