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 (wdPrimary FooterStor y) 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\LASIKInform edConsentT emplate.do tx"
Set appWord = New Word.Application
With appWord
.WindowState = wdWindowStateNormal
.Visible = True
Set doc = .Documents.Add(strDoc)
.Selection.Find.ClearForma tting
End With
With doc
.Range.InsertFile ("C:\WVLCStudio\LASIKInfor medConsent Template.d otx")
With appWord.Selection.Find
.ClearFormatting
.Replacement.ClearFormatti ng
.MatchWholeWord = True
.Execute FindText:="%PatientName%", ReplaceWith:="Hello World", _
Replace:=wdReplaceAll
.Forward = True
.Wrap = wdFindContinue
End With
Set rng = ActiveDocument.StoryRanges (wdPrimary FooterStor y)
With rng
.Find.ClearFormatting
.Find.Text = "%PatientName%"
.Find.Replacement.ClearFor matting
.Find.Replacement.Text = "John Smith"
.Find.Execute Replace:=wdReplaceAll
End With
appWord.Selection.Find.Exe cute
.PrintPreview
End With
appWord.Quit
Set appWord = Nothing
Set doc = Nothing
End Sub
However, the code will only run properly one time. When run a second time it hangs on the Set rng = ActiveDocument.StoryRanges
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\LASIKInform
Set appWord = New Word.Application
With appWord
.WindowState = wdWindowStateNormal
.Visible = True
Set doc = .Documents.Add(strDoc)
.Selection.Find.ClearForma
End With
With doc
.Range.InsertFile ("C:\WVLCStudio\LASIKInfor
With appWord.Selection.Find
.ClearFormatting
.Replacement.ClearFormatti
.MatchWholeWord = True
.Execute FindText:="%PatientName%",
Replace:=wdReplaceAll
.Forward = True
.Wrap = wdFindContinue
End With
Set rng = ActiveDocument.StoryRanges
With rng
.Find.ClearFormatting
.Find.Text = "%PatientName%"
.Find.Replacement.ClearFor
.Find.Replacement.Text = "John Smith"
.Find.Execute Replace:=wdReplaceAll
End With
appWord.Selection.Find.Exe
.PrintPreview
End With
appWord.Quit
Set appWord = Nothing
Set doc = Nothing
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.Ran
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!!