Link to home
Start Free TrialLog in
Avatar of isurgyn
isurgyn

asked on

Problem with Error 462 when automating Word from Access

I am hoping someone can help me with what seems to be a relatively simple problem.  I am attempting to automate a Word 2007 document using Access 2007.  The code I am posting works perfectly to find and replace placeholder text in the Word document body as well as in the footer.

However, the code will only run properly one time.  When run a second time it hangs on the             Set rng = ActiveDocument.StoryRanges(wdPrimaryFooterStory) line of code and brings up Error 462 indicating that the remote server is unavailable.  The error will clear itself if I just close and reopen the Access form from which the code is launched.  Then it will again run perfectly one time.

Also, if I remove the portion of the code that pertains to updating the text in the footer, it runs perfectly without generating the error.

It seems to me that my code is not able to close out the Word object completely.  By closing the Access form ( or closing the Access application) the "remnant" of this Word object is cleared.  However, I need the code to be able to process multiple Word documents in succession so even closing the Access form each time would be tedious and truly pathetic.  

Anyway, it seems to me that the ActiveDocument method seems to have a memory that exists even with all of the .Quit and Set X = Nothing statements that I have in the code.

The code is a little simplistic right now but until I get the Error issue solved I haven't been concerned about making the routine a bit more sophisticated.

Thanks. Sorry I am am not a great VB programmer so please try to be gentle.



Private Sub Command1_Click()
Dim appWord As Word.Application
Dim doc As Word.Document
Dim rng As Word.Range
Dim strDoc As String

    strDoc = "C:\WVLCStudio\LASIKInformedConsentTemplate.dotx"

    Set appWord = New Word.Application
   
    With appWord
   
        .WindowState = wdWindowStateNormal
        .Visible = True
       
        Set doc = .Documents.Add(strDoc)
        .Selection.Find.ClearFormatting
       
    End With
   
    With doc
   
        .Range.InsertFile ("C:\WVLCStudio\LASIKInformedConsentTemplate.dotx")
       
        With appWord.Selection.Find
       
            .ClearFormatting
            .Replacement.ClearFormatting
            .MatchWholeWord = True
            .Execute FindText:="%PatientName%", ReplaceWith:="Hello World", _
                Replace:=wdReplaceAll
            .Forward = True
            .Wrap = wdFindContinue
           
        End With
       
            Set rng = ActiveDocument.StoryRanges(wdPrimaryFooterStory)
            With rng
                .Find.ClearFormatting
                .Find.Text = "%PatientName%"
                .Find.Replacement.ClearFormatting
                .Find.Replacement.Text = "John Smith"
                .Find.Execute Replace:=wdReplaceAll
            End With
       
        appWord.Selection.Find.Execute
        .PrintPreview
       
    End With
     
    appWord.Quit
    Set appWord = Nothing
    Set doc = Nothing

End Sub
ASKER CERTIFIED SOLUTION
Avatar of Boyd (HiTechCoach) Trimmell, Microsoft Access MVP 2010-2015
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP 2010-2015
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of isurgyn
isurgyn

ASKER

Thank you so much for the quick responses.  I tried both solutions and they worked beautifully.

I did have a Compile error on the more elaborate coding solution on this line:

        With appWord..Range.Find

So I changed it to

        With appWord.ActiveDocument.Range.Find

and the compile error went away and the code runs beautifully.

I also found that the

doc.Close wdSaveChanges '(or wdDonotsavechanges if that suits your situation better

worked a bit unpredictably so deleted that and all is well for now.  As I make the code do more edits and auto print I may need to add it back in.

Thanks again.  You saved me hours of work!!