Why Does This Code Work SOOOO Slow?

[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

References used in the Access Module:
Visual Basic for Applications
Microsoft Access 9.0 Object Library
OLE Automation
Microsoft DAO 2.5/3.5 Compatibility
Now, about the code:

I have some Word mail merge docs that get fed by queries in the 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.

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.

As you will be able to tell, I'm not a programmer. What am I doing
wrong, or inefficiently, etc?

Any help/advice is much appreciated.

Thanks,

Bob

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

[Steve Jorgensen]

There is no obvious culprit, but mysterious things can sometimes happen with
automation. First, try stepping through the code, and see what lines have the
longest delay. If the delay is in one of your MSWord.Run lines, then for some
reason, your code in MS Word is running slower when automated. In that case,
you may have to add some sort of section/timestamp logging to the code that
runs in Word to see what part of it got slow.

On 22 Feb 2005 19:22:16 -0800, bl*******@longmire-co.com wrote:

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

References used in the Access Module:
Visual Basic for Applications
Microsoft Access 9.0 Object Library
OLE Automation
Microsoft DAO 2.5/3.5 Compatibility
Now, about the code:

I have some Word mail merge docs that get fed by queries in the 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.

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.

As you will be able to tell, I'm not a programmer. What am I doing
wrong, or inefficiently, etc?

Any help/advice is much appreciated.

Thanks,

Bob

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

[dilbert]

You also might want to replace your DCount() calls with SQL count
queries.