Follow up question on Printing Word Document from Excel Macro...
Posted by Bill Tanner on December 16, 1999 9:52 AM
I was using the code that Tom Morales suggested below and it worked fine, whether I was printing on document or more than one.
What I am doing is using Excel to print insurance policies. The worksheets include those forms which need to be completed -- they are completed by formula -- and the code is supposed to print those worksheets as well as open Word and print some forms from there. The code also controls the print order -- I print a couple of worksheets, print a couple of documnets, more worksheets, more documents, etc. So I am constantly switching back and forth between printing in Excel and printing in Word.
What is happening is that I print the first worksheets just fine. Then I run a subroutine that prints some documents and that works. Then I print more worksheets. Finally, I run another subroutine to print more documents and that is where it chokes. Specifically, it chokes on the first "Set mydoc" statement of the second subroutine -- no matter which one is placed second. The subroutines run fine individually; it's only when called back to back that they choke. The Error Message is "Automation Error 440" -- '-2147023174 (800706ba)' if that helps.
The first subroutine reads:
Sub Print_Policy_Validation()
Dim AppWord As Word.Application
Set AppWord = CreateObject("Word.Application.8")
AppWord.Visible = True 'This could be false, also
'Print Policy Validation Form
Set mydoc = Documents.Open(FileName:="C:\My Documents\Doc1.doc")
With AppWord
.ActiveDocument.PrintOut Background:=False
.Quit SaveChanges:=False 'Quit & close without changes
End With
Set AppWord = Nothing 'release reference to it
End Sub
and the second reads:
Sub Print_AK_Common() Set mydoc = Documents.Open(FileName:="C:\My Documents\Doc2.doc") .Quit SaveChanges:=False 'Quit & close without changes Set AppWord = Nothing 'release reference to it
Dim AppWord As Word.Application
Set AppWord = CreateObject("Word.Application.8")
AppWord.Visible = True 'This could be false, also
'Print Common Policy Conditions, Alaska Cancellation Endorsement and Alaska Attorney's Fees Endorsement
With AppWord
.ActiveDocument.PrintOut Background:=False
Set mydoc = Documents.Open(FileName:="C:\My Documents\Doc3.doc")
With AppWord
.ActiveDocument.PrintOut Background:=False
Set mydoc = Documents.Open(FileName:="C:\ Word\My Documents\Doc4.doc")
With AppWord
.ActiveDocument.PrintOut Background:=False
End With
End With
End With
End Sub
Anybody have any suggestions?
Thanks very much
Bill Tanner
Posted by Tom Morales on December 16, 1999 11:14 AM
When things go wrong and all is dark, I try something like:
Sub Print_AK_Common()
on Error Resume Next
Dim AppWord As Word.Application
' and so on
If that doesn't work, take two aspirin, and post back in the morning.
Posted by Bill Tanner on December 17, 1999 6:28 AM
Maybe I can make this simplier...
If I run this code one time, it appears to work fine. If I run it a second time, it chokes at "Set mydoc". It opens Word a second time but it doesn't open the document. Why not? Intuitively, I would say that the first run didn't close the document? / application? properly so it isn't found on the second run.
Sub Print_Policy_Validation()
'On Error Resume Next
Dim AppWord As Word.Application
Set AppWord = CreateObject("Word.Application.8")
AppWord.Visible = True 'This could be false, also
'Print Policy Validation Form
Set mydoc = Documents.Open(FileName:="C:\My Documents\Doc1.doc")
With AppWord
.ActiveDocument.PrintOut Background:=False
.Quit SaveChanges:=False 'Quit & close without changes
End With
Set AppWord = Nothing 'release reference to it
End Sub
Any help anybody could give would be very much appreciated...
Bill Tanner
Posted by Bill Tanner on December 17, 1999 6:36 AM
One other thing...
In order to get the macro to run again,I have to manually open Word and then close it again. Then the macro will run again without choking.
Bill Tanner
Posted by Tom Morales on December 17, 1999 8:19 AM
Re: One other thing...
Try releasing mydoc when you're finished with a document. Add the following statement
set mydoc = Nothing
when you release AppWord.
Posted by Bill Tanner on December 17, 1999 9:04 AM
Very strange...
I did as you suggested and it still worked the first time I ran it but not the second time. It still opens Word but chokes on the file name that it accepted the first time through.
So then I added the code a second time -- first when leaving the macro (as you suggested) and the second, just before making the file call that I am choking on. On the second run through, it accepts the command to set mydoc to nothing but still chokes when told to set mydoc to my file name.
I am afraid that makes no sense to me at all.
Any other ideas?
Sub Print_Policy_Validation()
'On Error Resume Next
Dim AppWord As Word.Application
Set AppWord = CreateObject("Word.Application.8")
AppWord.Visible = True 'This could be false, also
'Print Policy Validation Form
Set mydoc = Nothing
Set mydoc = Documents.Open(FileName:="C:\Doc1.doc")
With AppWord
.ActiveDocument.PrintOut Background:=False
.Quit SaveChanges:=False 'Quit & close without changes
End With
Set mydoc = Nothing
Set AppWord = Nothing 'release reference to it
End Sub
Bill Tanner
Posted by Bill Tanner on December 17, 1999 10:32 AM
I found the answer -- I just don't understand it...
I found something at microsoft.com that appears to be on point -- my automation error is -2147023174.
Now that I have found it, I'm afraid that I don't know what to do with it. Maybe you can tell me how to modify my code to incorporate the solution that they speak of???
Thanks again for all your help...
Bill Tanner
Posted by Tom Morales on December 17, 1999 11:39 AM
Re: I found the answer -- I just don't understand it...
Bill - I believe the instructions are telling you to change the offending line to:
Set mydoc = AppWord.Documents.Open(FileName:="C:\My Documents\Doc2.doc")
Tom
Posted by Bill Tanner on December 17, 1999 12:55 PM
It works! Thank you so much...
Words cannot express how much I appreciate all your help.
This stupid project has been my whole life for a year and now, with your help, I am 90% done. It was an awfully important missing link.
Regards and Seasons Greetings to all
Bill Tanner
