Problem With CreateObject in Access

[ronnies]

I am having a problem concerning the following code:

Dim strFileName As String
Dim wrdDoc As Object
Dim objWD As Object
Dim strSQL As String
strSQl = "c:\My Documents\Letters\TestLetter.doc"
Set objWD = CreateObject("Word.Application&quot
Set wrdDoc = objWD.Documents.Open(strFileName)
objWD.ActiveDocument.MailMerge.Execute
objWD.ActiveDocument.PrintOut Background:=False
objWD.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
objWD.Quit

Any time there is an error during execution this leaves a copy of winword in memory, which can result in multiple copies of winword lost in memory. Adding objWD.Quit to the error traping routine only results in and endless loop.

Any Help ????

 

[kathryn]

Oh, yeah, been there done that. The reason I always get an infinite loop is that I forget to add an On Error resume next in your error handling section. Could that be your problem?

Kathryn

 

[ronnies]

Kathryn,

Thanks for the reply. My error recovery code is:

Exit_btnPrintClick:
Set objWD = Nothing
Set wrdDoc = Nothing
Exit Sub
Err_btnPrint_Click:
MsgBox err.description
Resume Exit_btnPrint_Click

But this still leaves an instance of winword hidden in memory. How can I always be sure and remove the instance of winword from memory?

 

[Quehay]

This is a total stab in the dark, but could the reference to wrdDoc in your Exit label code subsequent to the final reference to objWD, even if it only sets it to nothing, bring a fresh reference to objWD via the parent relation? I'm no automation wiz but there are some instances (pardon the double entendre) where object variables can reset themselves based on merely mentioning them again.

 

[kathryn]

Try this

Exit_btnPrintClick:
on error resume next 'NEW LINE
objword.quit 'NEW LINE
Set objWD = Nothing
Set wrdDoc = Nothing
Exit Sub
Err_btnPrint_Click:
MsgBox err.description
Resume Exit_btnPrint_Click



Kathryn

 

[Guest_imported]

Dear Ronnies

Do you get a message about Access being unab;e to create and ActiveX object, or something like that?

This puzzled me for a while, but in order to make sure it works regardless of whether Word is running or not, you need an error trap for error 429, which means something like, "Word already running". In response, you run GetObject instead of CreatObject.

Have a go (I'm not sure if I've got it the right way around!) and get back to me if you can't figure it out, so I can dig out my actual code and send it to you .

Best regards

Paul

 

[GeekGirlau]

Hi Ronnies,

I'd try the following:

Dim strFileName As String
' **************************************
Dim objWD As Word.Application
Dim wrdDoc As Word.Document
' **************************************
Dim strSQL As String


On Error GoTo Err_btnPrint_Click
strSQl = "c:\My Documents\Letters\TestLetter.doc"

On Error Resume Next
Set objWD = GetObject(, "Word.Application&quot

If Err.Number = 429 Then
Set objWD = CreateObject("Word.Application&quot
End If

On Error GoTo Err_btnPrint_Click

Set wrdDoc = objWD.Documents.Open(strFileName)

With objWD
.ActiveDocument.MailMerge.Execute
.ActiveDocument.PrintOut Background:=False
.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
End With


Exit_btnPrintClick:
On Error Resume Next
objWD.Quit
Set wrdDoc = Nothing
Set objWD = Nothing
Exit Sub

Err_btnPrint_Click:
MsgBox err.description
Resume Exit_btnPrint_Click

It's usually a good idea to Dim your variables as specifically as possible rather than as a generic object. As Quehay mentioned, you should set the document object to nothing, then the application.