• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 503
  • Last Modified:

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
0
isurgyn
Asked:
isurgyn
2 Solutions
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
Try changing this line of VBA code:

 Set rng = ActiveDocument.StoryRanges(wdPrimaryFooterStory)

Open in new window


to

 Set rng = appWord.ActiveDocument.StoryRanges(wdPrimaryFooterStory)

Open in new window

0
 
GrahamSkanRetiredCommented:
The code isn't bad, but here is a version with a few tweaks.

1. It uses any existing instance if the word application, only creating a new one if necessary.

2. You can use the Selection.Find object or a Range.Find object, but you seem to be confusing the two. If you use a Range object, there is no need to clear the formatting, because it is a new Range.Find object each time. There is only one Selection.Find object so resetting the formatting may be necessary. I always advise using a Range instead of the Selection, if possible.

3. Although you capture the document object (doc), you use the ActiveDocument instead on the failing line. If you do use it, do add the application qualifier as TheHiTechCoach demonstrates, otherwise the code assumes that you are talking about the ActiveDocument from the previous application object which you destroyed with the Quit method.

4. On the same tack. You don't actually close the document.

5. I have also removed the Set objects to Nothing lines, which I believe to do Nothing. They are a futile attempt to get round a rare memory leak problem with COM, but the action does not more than VB does automatically when the Sub finishes.
Private Sub Command1_Click()
Dim appWord As Word.Application
Dim doc As Word.Document
Dim rng As Word.Range
Dim strDoc As String
Dim bAlready As Boolean

    strDoc = "C:\WVLCStudio\LASIKInformedConsentTemplate.dotx"
    
    'Try to use an existing instance of the word application
    On Error Resume Next 'no simple way to to for an existing instance, so supress errors
        Set appWord = GetObject(, "Word.Application")
    On Error GoTo 0 'restart error checking
    
    If appWord Is Nothing Then 'the GetObject failed
        Set appWord = CreateObject("Word.Application")
    Else
        bAlready = True
    End If
    
    With appWord
   
        .WindowState = wdWindowStateNormal
        .Visible = True
       
        Set doc = .Documents.Add(strDoc)
       
    End With
   
    With doc
   
        .Range.InsertFile ("C:\WVLCStudio\LASIKInformedConsentTemplate.dotx")
       
        With appWord.Range.Find
       
            .MatchWholeWord = True
            .Execute FindText:="%PatientName%", ReplaceWith:="Hello World", _
                Replace:=wdReplaceAll
            .Forward = True
            .Wrap = wdFindContinue
           
        End With
       
        Set rng = doc.StoryRanges(wdPrimaryFooterStory)
        With rng.Find
            .Text = "%PatientName%"
            .Replacement.Text = "John Smith"
            .Execute Replace:=wdReplaceAll
        End With
        
        .PrintPreview
       
    End With
    doc.Close wdSaveChanges '(or wdDonotsavechanges if that suits your situation better
    If bAlready Then
        appWord.Quit
    End If
End Sub

Open in new window

0
 
isurgynAuthor Commented:
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!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now