VERY Slow Execution When Word Maco Is Called

[blongmire]

.... I know just enough to be dangerous, but the real danger is that I
might fall asleep and hit my head on my keyboard waiting for this code
to finish executing.

Some preliminaries: WinXP Pro, Office 2000, DDE linkage between Access
and Word mail merge docs

Now, about the code:

I have some Word mail merge docs that get fed by queries in an Access
db that contains this code. The code below successfully opens each Word
file, runs a macro within Word that merges the data to a new document,
closes/saves the new merged Word file and then does it again through
the For Next loop. It just does it REAL slowly once it begins executing
the mail merge to a new document.

The macro in Word works fast if I just open Word, open the doc and run
it. It takes about 5 seconds to merge 75-100 records. The code below
takes about 2 minutes. It takes most of its time merging from one
record to the next, not opening, saving or closing docs.

What am I doing wrong, or inefficiently, etc?

Any help/advice is much appreciated.

Thanks,

Bob

' Microsoft Access 2000 function that calls Word and runs merges

Function ProduceMailMergeDocs()

ReDim myWordLocation(1 To 10) As String, myWordDocName(1 To 10) As
String, cnt(1 To 10)
Dim MSWord As Object


myWordLocation(1) = "C:\Word\Folder1\"
myWordLocation(2) = "C:\Word\Folder2\"
myWordLocation(3) = "C:\Word\Folder3\"
myWordLocation(4) = "C:\Word\Folder4\"
myWordLocation(5) = "C:\Word\Folder5\"
myWordLocation(6) = "C:\Word\Folder6\"
myWordLocation(7) = "C:\Word\Folder7\"


myWordDocName(1) = "Document1"
myWordDocName(2) = "Document2"
myWordDocName(3) = "Document3"
myWordDocName(4) = "Document4"
myWordDocName(5) = "Document5"
myWordDocName(6) = "Document6"
myWordDocName(7) = "Document7"


cnt(1) = DCount("[ProspectNo]", "AccessQuery1")
cnt(2) = DCount("[ProspectNo]", "AccessQuery2")
cnt(3) = DCount("[ProspectNo]", "AccessQuery3")
cnt(4) = DCount("[ProspectNo]", "AccessQuery4")
cnt(5) = DCount("[ProspectNo]", "AccessQuery5")
cnt(6) = DCount("[ProspectNo]", "AccessQuery6")
cnt(7) = DCount("[ProspectNo]", "AccessQuery7")


Set MSWord = CreateObject(Class:="Word.Application")
MSWord.Visible = True
MSWord.Activate


For n = 1 To 7
If cnt(n) > 0 Then
MSWord.Documents.Open FileName:=myWordLocation(n) &
myWordDocName(n) & ".doc"
MSWord.Run ("MailMerge")
MSWord.Run ("MailMerge2")
MSWord.ActiveDocument.SaveAs myWordLocation(n) &
myWordDocName(n) & " (" & cnt(n) & ")" & ".doc"
MSWord.ActiveDocument.Close
MSWord.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges


Else
End If
Next n


MSWord.Quit
End Function

 

[Howard Kaikow]

See my response on microsoft.public.word.vba.beginners.