This code appears to work correctly when run initially (well it does what I
expect it to do) .
On subsequent runs it produces an Automation Error where indicated.

I am using Excel 97 and Word 97
I have set up references to Word 8.0 Object library and Office 8.0 Object
library

After the intial run it seems as if Word is not shut down completely !!!


Any / all help greatly apreciated
TIA

Here is the code I omitted to send

Sub MailMergeTest()

Dim WD As Object
Set WD = CreateObject("Word.Application")
WD.Application.DisplayAlerts = wdAlertsNone
WD.Documents.Open (ThisWorkbook.Path & "\PrivList Labels.doc")

With ActiveDocument 'THIS LINE PRODUCES AUTOMATION ERROR IF RUN
TWICE
With .MailMerge
If .State < wdMainAndDataSource Then
.OpenDataSource _
Name:=ThisWorkbook.Path & "\PrivList.csv", _
LinkToSource:=True
End If
.Destination = wdSendToPrinter
.Execute
End With
.Close SaveChanges:=wdDoNotSaveChanges
End With

WD.Application.DisplayAlerts = wdAlertsAll
WD.Application.EnableEvents = True
WD.Quit SaveChanges:=wdDoNotSaveChanges
Set WD = Nothing

End Sub

I have now deleted the line (near the end of the procedure)
WD.Application.EnableEvents = True
and it still doesn't work correctly

Regards

JRB

Just guessing, but I'll bet that the MailMerge isn't fully executed by the
time you get to Set WD = Nothing. And that open process is not allowing you
to destroy the WD object. Those DDE links can be slow. I couldn't see any
property of the MailMerge object that you could check to see when it's
complete.

One klunky workaround that I can think of is to merge it to a new document
instead of a printer. Then you can check the name of the new document to
determine when it's complete

Dim wdDoc as Object

Set wdDoc = WD.Documents.Open(...)

With wdDoc
...
Do
DoEvents
Loop Until ActiveDocument.Name < wdDoc.Name

ActiveDocument.PrintOut
End With

I don't know if that will work. The ActiveDocument test may pass before the
merge is complete. You might post a question in one of the Word groups (I
think there's one for mailmerges) and see if they have any bright ideas.
All this is predicated on the assumption that I'm right about the merge
process not finishing before you kill your objects.

You might start by testing that theory. How long does it take to start
printing? If it's, say, 10 seconds, then you could stick a loop in there
like

Dim dNow as Date

dNow = Now

Do
DoEvents
Loop Until (Now-TimeSerial(0,0,10)) dNow

Then kill the word object. If you can run it again without an error, then I
think it's pretty clear that merge is holding that object open.

Good luck, and let me know if you find a satisfactory answer.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"JRB" wrote in message
...
Here is the code I omitted to send

Sub MailMergeTest()

Dim WD As Object
Set WD = CreateObject("Word.Application")
WD.Application.DisplayAlerts = wdAlertsNone
WD.Documents.Open (ThisWorkbook.Path & "\PrivList Labels.doc")

With ActiveDocument 'THIS LINE PRODUCES AUTOMATION ERROR IF RUN
TWICE
With .MailMerge
If .State < wdMainAndDataSource Then
.OpenDataSource _
Name:=ThisWorkbook.Path & "\PrivList.csv", _
LinkToSource:=True
End If
.Destination = wdSendToPrinter
.Execute
End With
.Close SaveChanges:=wdDoNotSaveChanges
End With

WD.Application.DisplayAlerts = wdAlertsAll
WD.Application.EnableEvents = True
WD.Quit SaveChanges:=wdDoNotSaveChanges
Set WD = Nothing

End Sub

I'll haven't tried your suggestion yet but I am using Acrobat Distiller as
the printer and it does produce the requisite number of pages (89 pages) and
completes the printing in approx 8 seconds.

What I have noticed is:
After the first (successful) run I looked in TaskManager to see if Word was
still running ... It wasn't

After the second (unsuccessful) run when the procedure fails with the
Automation Error I open Task Manger and not surprisingly Winword is running.
But if I then end the Winword application using TaskManager .... the
procedure will then work next time around

When my head stops spinning I will try your suggestion about adding a time
delay of 10 secs before killing the object.
Klunky or not I need to find a solution.

Is there a way to pause execution until the print process is completed ?

Regards and thanks for your help
Always grateful for your help and advice



"Dick Kusleika" wrote in message
...
JRB

Just guessing, but I'll bet that the MailMerge isn't fully executed by the
time you get to Set WD = Nothing. And that open process is not allowing
you
to destroy the WD object. Those DDE links can be slow. I couldn't see
any
property of the MailMerge object that you could check to see when it's
complete.

One klunky workaround that I can think of is to merge it to a new document
instead of a printer. Then you can check the name of the new document to
determine when it's complete

Dim wdDoc as Object

Set wdDoc = WD.Documents.Open(...)

With wdDoc
...
Do
DoEvents
Loop Until ActiveDocument.Name < wdDoc.Name

ActiveDocument.PrintOut
End With

I don't know if that will work. The ActiveDocument test may pass before
the
merge is complete. You might post a question in one of the Word groups (I
think there's one for mailmerges) and see if they have any bright ideas.
All this is predicated on the assumption that I'm right about the merge
process not finishing before you kill your objects.

You might start by testing that theory. How long does it take to start
printing? If it's, say, 10 seconds, then you could stick a loop in there
like

Dim dNow as Date

dNow = Now

Do
DoEvents
Loop Until (Now-TimeSerial(0,0,10)) dNow

Then kill the word object. If you can run it again without an error, then
I
think it's pretty clear that merge is holding that object open.

Good luck, and let me know if you find a satisfactory answer.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"JRB" wrote in message
...
Here is the code I omitted to send

Sub MailMergeTest()

Dim WD As Object
Set WD = CreateObject("Word.Application")
WD.Application.DisplayAlerts = wdAlertsNone
WD.Documents.Open (ThisWorkbook.Path & "\PrivList Labels.doc")

With ActiveDocument 'THIS LINE PRODUCES AUTOMATION ERROR IF RUN
TWICE
With .MailMerge
If .State < wdMainAndDataSource Then
.OpenDataSource _
Name:=ThisWorkbook.Path & "\PrivList.csv", _
LinkToSource:=True
End If
.Destination = wdSendToPrinter
.Execute
End With
.Close SaveChanges:=wdDoNotSaveChanges
End With

WD.Application.DisplayAlerts = wdAlertsAll
WD.Application.EnableEvents = True
WD.Quit SaveChanges:=wdDoNotSaveChanges
Set WD = Nothing

End Sub

JRB

"JRB" wrote in message
...
I'll haven't tried your suggestion yet but I am using Acrobat Distiller as
the printer and it does produce the requisite number of pages (89 pages)
and
completes the printing in approx 8 seconds.

What I have noticed is:
After the first (successful) run I looked in TaskManager to see if Word
was
still running ... It wasn't

After the second (unsuccessful) run when the procedure fails with the
Automation Error I open Task Manger and not surprisingly Winword is
running.
But if I then end the Winword application using TaskManager .... the
procedure will then work next time around

It's probably not the Word application, but the Word document that's
remaining open. So you won't see the Word app in task manager, but it's
really still open behind the scenes. You don't have any problem with the
CreateObject line because it's not using the same instance of Word, but when
you try to access the ActiveDocument, that's where it runs into a conflict.


When my head stops spinning I will try your suggestion about adding a time
delay of 10 secs before killing the object.
Klunky or not I need to find a solution.

Is there a way to pause execution until the print process is completed ?

Other that a Do..Loop like I've described, I don't know of any way. It's
not so much when the printing is completed, but when it starts. (This may
not really apply using Distiller compred to a normal spooled printer.) I
can close a Word document once IT'S done printing, but before the printer is
done. Once the print job is sent, Word and the document can be closed, even
if the print spooler is still working. That makes me think that Word isn't
done sending information to Distiller. Which makes sense, because your
procedure ends about a microsecond after it starts, which is hardly enough
time for Word to do its thing.

The point is, you don't have to pause the macro until printing is done
(probably), just until Word has finished sending its info to Distiller. It
won't hurt to pause it that long, using a Do..Loop, particularly while
you're testing. But before you go final with it, you might experiment with
shorter pause times so you don't have to wait so long.

I wonder if you can hook into that Word document at the start of your sub
and kill it. Untested, but something like this

Dim wdAppCleanUp as Object
Dim wdDocCleanUp as Object

On Error Resume Next
Set wdDocCleanUp = GetObject(,"PrivList Labels.doc")
Set wdAppCleanUp = wdDoc.Parent
wdDocCleanUp.Close
wdAppCleanUp.Quit
On Error Goto 0

'Rest of your code here

So before you ever run the mailmerge stuff, you try to hook into that
orphaned instance of the Word doc and clean it up (it should be done by the
next time you run the sub). I don't know if you can use GetObject in that
manner, but it might be worth a try if you get desperate. The On Error will
prevent an problems on the first run or if the document isn't orphaned for
some reason.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

Here is another thought on the problem.

in your original code, I would try changing

With ActiveDocument 'THIS LINE PRODUCES AUTOMATION ERROR IF RUN

to qualify it with the wd application

With wd.ActiveDocument 'THIS LINE PRODUCES AUTOMATION ERROR IF RUN

If that doesn't fix it, I would try qualify all the references that are
currently qualified using the With construct so each is standalone starting
from the WD (word application) object.

This is generally the cause of the problem you are having. (having a
reference to the automated application that can not be released).

--
Regards,
Tom Ogilvy


"JRB" wrote in message
...
I'll haven't tried your suggestion yet but I am using Acrobat Distiller as
the printer and it does produce the requisite number of pages (89 pages)
and
completes the printing in approx 8 seconds.

What I have noticed is:
After the first (successful) run I looked in TaskManager to see if Word
was
still running ... It wasn't

After the second (unsuccessful) run when the procedure fails with the
Automation Error I open Task Manger and not surprisingly Winword is
running.
But if I then end the Winword application using TaskManager .... the
procedure will then work next time around

When my head stops spinning I will try your suggestion about adding a time
delay of 10 secs before killing the object.
Klunky or not I need to find a solution.

Is there a way to pause execution until the print process is completed ?

Regards and thanks for your help
Always grateful for your help and advice



"Dick Kusleika" wrote in message
...
JRB

Just guessing, but I'll bet that the MailMerge isn't fully executed by
the
time you get to Set WD = Nothing. And that open process is not allowing
you
to destroy the WD object. Those DDE links can be slow. I couldn't see
any
property of the MailMerge object that you could check to see when it's
complete.

One klunky workaround that I can think of is to merge it to a new
document
instead of a printer. Then you can check the name of the new document
to
determine when it's complete

Dim wdDoc as Object

Set wdDoc = WD.Documents.Open(...)

With wdDoc
...
Do
DoEvents
Loop Until ActiveDocument.Name < wdDoc.Name

ActiveDocument.PrintOut
End With

I don't know if that will work. The ActiveDocument test may pass before
the
merge is complete. You might post a question in one of the Word groups
(I
think there's one for mailmerges) and see if they have any bright ideas.
All this is predicated on the assumption that I'm right about the merge
process not finishing before you kill your objects.

You might start by testing that theory. How long does it take to start
printing? If it's, say, 10 seconds, then you could stick a loop in
there
like

Dim dNow as Date

dNow = Now

Do
DoEvents
Loop Until (Now-TimeSerial(0,0,10)) dNow

Then kill the word object. If you can run it again without an error,
then
I
think it's pretty clear that merge is holding that object open.

Good luck, and let me know if you find a satisfactory answer.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"JRB" wrote in message
...
Here is the code I omitted to send

Sub MailMergeTest()

Dim WD As Object
Set WD = CreateObject("Word.Application")
WD.Application.DisplayAlerts = wdAlertsNone
WD.Documents.Open (ThisWorkbook.Path & "\PrivList Labels.doc")

With ActiveDocument 'THIS LINE PRODUCES AUTOMATION ERROR IF
RUN
TWICE
With .MailMerge
If .State < wdMainAndDataSource Then
.OpenDataSource _
Name:=ThisWorkbook.Path & "\PrivList.csv", _
LinkToSource:=True
End If
.Destination = wdSendToPrinter
.Execute
End With
.Close SaveChanges:=wdDoNotSaveChanges
End With

WD.Application.DisplayAlerts = wdAlertsAll
WD.Application.EnableEvents = True
WD.Quit SaveChanges:=wdDoNotSaveChanges
Set WD = Nothing

End Sub

Once again this group has solved my problems - Thanks to you both for your
swift and helpful responses

I have done as Tom suggested and removed the With construct and for good
measure qualified each of the lines with the wd application and it works
perfectly each time - even in a loop.
(everything is now crossed that it will work with a real printer)

You guys are incredible



"Tom Ogilvy" wrote in message
...
Here is another thought on the problem.

in your original code, I would try changing

With ActiveDocument 'THIS LINE PRODUCES AUTOMATION ERROR IF RUN

to qualify it with the wd application

With wd.ActiveDocument 'THIS LINE PRODUCES AUTOMATION ERROR IF RUN

If that doesn't fix it, I would try qualify all the references that are
currently qualified using the With construct so each is standalone
starting
from the WD (word application) object.

This is generally the cause of the problem you are having. (having a
reference to the automated application that can not be released).

--
Regards,
Tom Ogilvy


"JRB" wrote in message
...
I'll haven't tried your suggestion yet but I am using Acrobat Distiller
as
the printer and it does produce the requisite number of pages (89 pages)
and
completes the printing in approx 8 seconds.

What I have noticed is:
After the first (successful) run I looked in TaskManager to see if Word
was
still running ... It wasn't

After the second (unsuccessful) run when the procedure fails with the
Automation Error I open Task Manger and not surprisingly Winword is
running.
But if I then end the Winword application using TaskManager .... the
procedure will then work next time around

When my head stops spinning I will try your suggestion about adding a
time
delay of 10 secs before killing the object.
Klunky or not I need to find a solution.

Is there a way to pause execution until the print process is completed ?

Regards and thanks for your help
Always grateful for your help and advice



"Dick Kusleika" wrote in message
...
JRB

Just guessing, but I'll bet that the MailMerge isn't fully executed by
the
time you get to Set WD = Nothing. And that open process is not
allowing
you
to destroy the WD object. Those DDE links can be slow. I couldn't
see
any
property of the MailMerge object that you could check to see when it's
complete.

One klunky workaround that I can think of is to merge it to a new
document
instead of a printer. Then you can check the name of the new document
to
determine when it's complete

Dim wdDoc as Object

Set wdDoc = WD.Documents.Open(...)

With wdDoc
...
Do
DoEvents
Loop Until ActiveDocument.Name < wdDoc.Name

ActiveDocument.PrintOut
End With

I don't know if that will work. The ActiveDocument test may pass
before
the
merge is complete. You might post a question in one of the Word
groups
(I
think there's one for mailmerges) and see if they have any bright
ideas.
All this is predicated on the assumption that I'm right about the
merge
process not finishing before you kill your objects.

You might start by testing that theory. How long does it take to
start
printing? If it's, say, 10 seconds, then you could stick a loop in
there
like

Dim dNow as Date

dNow = Now

Do
DoEvents
Loop Until (Now-TimeSerial(0,0,10)) dNow

Then kill the word object. If you can run it again without an error,
then
I
think it's pretty clear that merge is holding that object open.

Good luck, and let me know if you find a satisfactory answer.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"JRB" wrote in message
...
Here is the code I omitted to send

Sub MailMergeTest()

Dim WD As Object
Set WD = CreateObject("Word.Application")
WD.Application.DisplayAlerts = wdAlertsNone
WD.Documents.Open (ThisWorkbook.Path & "\PrivList
Labels.doc")

With ActiveDocument 'THIS LINE PRODUCES AUTOMATION ERROR IF
RUN
TWICE
With .MailMerge
If .State < wdMainAndDataSource Then
.OpenDataSource _
Name:=ThisWorkbook.Path & "\PrivList.csv", _
LinkToSource:=True
End If
.Destination = wdSendToPrinter
.Execute
End With
.Close SaveChanges:=wdDoNotSaveChanges
End With

WD.Application.DisplayAlerts = wdAlertsAll
WD.Application.EnableEvents = True
WD.Quit SaveChanges:=wdDoNotSaveChanges
Set WD = Nothing

End Sub

Everything seems to work extremely well ... except that I would like to keep
the Word application hidden.
I have inserted :
WD.Application.ScreenUpdating = False
WD.Application.Visible = False
prior to the line which opens the document (see below)
But after the printer dialog box is closed the Word application pops up
during the printing process
I would like to prevent this happening ... Have you any suggestions

........................
Dim WD As Object
Set WD = CreateObject("Word.Application")
WD.Application.DisplayAlerts = wdAlertsNone

WD.Application.ScreenUpdating = False ' ......... This is
probably superfluous
WD.Application.Visible = False

WD.Documents.Open (ThisWorkbook.Path & "\PrivList Labels.doc")
If WD.ActiveDocument.MailMerge.State < wdMainAndDataSource Then
WD.ActiveDocument.MailMerge.OpenDataSource _
Name:=ThisWorkbook.Path & "\PrivList.csv", _
LinkToSource:=True
End If
WD.ActiveDocument.MailMerge.Destination = wdSendToPrinter
WD.ActiveDocument.MailMerge.Execute
WD.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
WD.Application.DisplayAlerts = wdAlertsAll
WD.Quit SaveChanges:=wdDoNotSaveChanges
Set WD = Nothing
...........................................