Please help speed up this process

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am doing a large mailing (20,000). Basically I open MS Word templates,
update bookmarks, print to print file and loop to the next one. This process
is too slow to finish in my lifetime.

Is there a way to speed this up? Code Follows:

Dim rstl As ADODB.Recordset
Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Dim appword As Object
Dim Docs As Object

'OPEN MAILING FILE DATABASE & TABLE
Set rstl = New ADODB.Recordset
With rstl
.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\MAIL\Mailing.mdb;"
.Open "Mailing", , , , adCmdTable
End With

'LOOP THROUGH TABLE
j = 0
Do Until rstl.EOF
j = j + 1

strSched = "C:\MAIL\Mailing.dot"

'OPEN MS WORD
Set appword = CreateObject("Word.Application")
Set Docs = appword.Documents

Call UpdateWordDoc(j)

With appword
.ActiveDocument.Fields.Update
.ActiveDocument.PrintOut Background:=True,
OutputFileName:="C:\MAIL\Reports\" + strFileName ,_
Item:=wdPrintDocumentContent, Copies:=1, Printtofile:=True, Collate:=True
i = 0
Do Until Dir("C:\MAIL\Reports\" + strFileName) <> ""
i = i + 1
Loop
.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
End With

appword.Quit
Set appword = Nothing
Set Docs = Nothing
rstl.MoveNext
Loop

Thanks for your advise!!
 
Al said:
I am doing a large mailing (20,000). Basically I open MS Word
templates, update bookmarks, print to print file and loop to the next
one. This process is too slow to finish in my lifetime.

Is there a way to speed this up? Code Follows:

Dim rstl As ADODB.Recordset
Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Dim appword As Object
Dim Docs As Object

'OPEN MAILING FILE DATABASE & TABLE
Set rstl = New ADODB.Recordset
With rstl
.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\MAIL\Mailing.mdb;"
.Open "Mailing", , , , adCmdTable
End With

'LOOP THROUGH TABLE
j = 0
Do Until rstl.EOF
j = j + 1

strSched = "C:\MAIL\Mailing.dot"

'OPEN MS WORD
Set appword = CreateObject("Word.Application")
Set Docs = appword.Documents

Call UpdateWordDoc(j)

With appword
.ActiveDocument.Fields.Update
.ActiveDocument.PrintOut Background:=True,
OutputFileName:="C:\MAIL\Reports\" + strFileName ,_
Item:=wdPrintDocumentContent, Copies:=1, Printtofile:=True,
Collate:=True i = 0
Do Until Dir("C:\MAIL\Reports\" + strFileName) <> ""
i = i + 1
Loop
.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
End With

appword.Quit
Set appword = Nothing
Set Docs = Nothing
rstl.MoveNext
Loop

Thanks for your advise!!
Click to expand...

One thing is for sure: you can save a *lot* of time if you don't open
and close Word inside the loop. Why not move those lines outside the
loop?
 
Back
Top