Very Slow Word Macro Execution

[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]

1st step would be to eliminated unnecessary object references, in particular
to your MsWord object, especially since the references are in a loop.

Look into using

With MSWord

End With

And eliminate the unnecessary references within the With ... End With. For
example:

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


--
http://www.standards.com/; See Howard Kaikow's web site.

... 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