Word automation started giving errors - Now have to close and re-create Word object
Hi,
I have a VB6 program that performs Word automation. It processes a batch of invoices. Each invoice's details are populated into an existing Word document using Word find & replace commands. The document is then printed to a PDF and emailed. Usually the batch will be between 15 and 40 invoices.
i.e.
create word object
For each invoice
open word document
populate word document using find & replace
print word document
email word document
close word document
Next
close word object (MSWord.quit : Set MSWord = Nothing)
My VB code to automate Word is along the lines of:
Dim MSWord as object
Set MSWord = CreateObject("word.application","localhost")
' To Open Document
With MSWord
.Documents.Open Filename:=FileToOpen, etc
End With
' To Close Doucment
With MSWord
.ActiveDocument.Close SaveChanges:=0
End With
' To Print Document
With MSWord
.ActiveDocument.PrintOut FileName:="", etc
End With
' To Find & Replace
With MSWord
.Selection.Find.ClearFormatting
.Selection.Find.Replacement.ClearFormatting
With .Selection.Find
.Text = FindText
.Replacement.Text = ReplaceText
.Forward = True
.Wrap = 1 'wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
.Selection.Find.Execute replace:=1 'wdReplaceOne
End With
This program has worked for years without any problems. But now when I run it, it gives me errors when trying to execute the Word commands, but only when its processing say the 12th+ invoice. If I run the program again it will fail on a random invoice but usually on the 12th or more; It could be the 18th or 20th. It can fail on the openening of the Word document, the find & replace, or the print.
Sometimes the error is 5097 which I believe is a Word memory error. But my system has 16gb and no other apps running.
Running the program for a batch of 10 or less invoices seems to work ok. So this suggest maybe it is indeed a memory problem? But I cannot fathom why this has just started when it has worked fine for so long.
For testing purposes I modified my program to not print/generate a PDF and to not email. So its just opening, find and replacing and closing, and it still gives me errors. I also modified it to not find and replace, so it just opened and closed documents. That weirdly resulted in Word putting my document into its list of disable items.
*** RESOLVED (Kind of)
I finally ended up closing the Word object after each document had been printed and emailed, and then re-creating the Word object before opening the next document in the batch. This has cured the problem but I still can't understand why the program started giving me errors after working fine for so many years.
***
Re: Word automation started giving errors - Now have to close and re-create Word obje
The closing of Word and actively set the object to nothing should have done always.
Otherwise you would end up with a lot of running instances of Word on the PC.
Re: Word automation started giving errors - Now have to close and re-create Word obje
Yeah, when automating Word from VB6 with an object variable (MSWord in the above), this is one case where it's very important to issue a Quit before the Word object is uninstantiated. Otherwise, you're severing your connection to Word, but leaving it running in memory (possibly invisibly).
And this is independent of whether or not you saved any of Word's document changes.
Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.
Re: Word automation started giving errors - Now have to close and re-create Word obje
Originally Posted by Arnoutdv
The closing of Word and actively set the object to nothing should have done always.
Otherwise you would end up with a lot of running instances of Word on the PC.
I was creating only one instance of Word at the start of the program and then closing it at the end (and setting to nothing), after all the documents had been processed. I wasn't creating a Word object for each document.
Now I have to create and close the Word object for each document processed.
When I say close. I am quitting it and setting it to nothing.
Re: Word automation started giving errors - Now have to close and re-create Word obje
The setting to Nothing isn't critically important in this case. The Quit is much more important. But there's no reason you can't use one copy of Word to do all your work.
Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.
Re: Word automation started giving errors - Now have to close and re-create Word obje
Originally Posted by Elroy
But there's no reason you can't use one copy of Word to do all your work.
Thats what I thought and thats what I was doing for years, but this week I started having problems with it as described in my original post. I have no idea why.
Re: Word automation started giving errors - Now have to close and re-create Word obje
I just scanned your code, but one thing I did see is the use of ActiveDocument. Personally, when doing this, I always create object variables for each opened document and use those variables for manipulating the documents. Let me find an example:
Code:
Set wrd = WordApp ' Basically just: Set wrd = CreateObject("Word.Application")
wrd.Visible = False
Set doc = WordAddDoc(wrd, sTemplateSpec)
WordSetTemplate doc ' I can talk more about this if you like.
'
InWordReplaceAllOccurencesInBody doc, "[Hospital]", HospitalName ' Just to make things easier.
WordSaveAs doc, sFileSpec ' Basically just: doc.SaveAs sFileSpec
wrd.Quit False ' THIS is very important!!!
' And then I just let the wrd variable fall out of scope, as I'm done with it.
I have little functions for much of this work, but that doc variable is just an Object variable. Here's thecode in that WordAddDoc function:
Code:
Public Function WordAddDoc(wrd As Object, Optional sTemplateSpec As String) As Object
' If sTemplateSpec isn't specified, the "Normal" template is used.
wrd.AutomationSecurity = msoAutomationSecurityForceDisable ' Disables all macros in all files opened programmatically, without showing any security alerts. MAPS doesn't use any Word macros.
If Len(sTemplateSpec) Then
Set WordAddDoc = wrd.Documents.Add(sTemplateSpec)
Else
Set WordAddDoc = wrd.Documents.Add
End If
wrd.AutomationSecurity = msoAutomationSecurityByUI ' Uses the security setting specified in the Security dialog box. Basically, we're just putting it back.
End Function
Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.
Re: Word automation started giving errors - Now have to close and re-create Word obje
Thanks for the code example. I will look into declaring the document as it's own object rather than using .ActiveDocument. I presume it's better to handle it this way then?
Re: Word automation started giving errors - Now have to close and re-create Word obje
Yes... mostly because .ActiveDocument is just that... the Active Document ... it may not be what you actually want or expect. I've had this problem before in Excel ... ActiveWorkbook ends up returning the default Book1 that's opened when Excel opens ... even after "opening" a new workbook. I'm guessing somehting simiolar is happening with Word.
Re: Word automation started giving errors - Now have to close and re-create Word obje
Originally Posted by nealb
Thanks for the code example. I will look into declaring the document as it's own object rather than using .ActiveDocument. I presume it's better to handle it this way then?
Yeah, sorry, been working on something else. But Techgnome nailed it. If you have object variables for each of your documents, you just don't need to worry about which one is active.
Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.
Re: Word automation started giving errors - Now have to close and re-create Word obje
Originally Posted by Elroy
I just scanned your code, but one thing I did see is the use of ActiveDocument. Personally, when doing this, I always create object variables for each opened document and use those variables for manipulating the documents. Let me find an example:
Code:
Set wrd = WordApp ' Basically just: Set wrd = CreateObject("Word.Application")
wrd.Visible = False
Set doc = WordAddDoc(wrd, sTemplateSpec)
WordSetTemplate doc ' I can talk more about this if you like.
'
InWordReplaceAllOccurencesInBody doc, "[Hospital]", HospitalName ' Just to make things easier.
WordSaveAs doc, sFileSpec ' Basically just: doc.SaveAs sFileSpec
wrd.Quit False ' THIS is very important!!!
' And then I just let the wrd variable fall out of scope, as I'm done with it.
Would you be kind enough to show me the code for how you deal with find and replace when the document is set as an object?
Also under .activedocument I was able to return a variable if the text was found or not by using TextFound = .selection.find.found but I can't seem to use this with the Doc object.
Re: Word automation started giving errors - Now have to close and re-create Word obje
Hi Nealb,
Ok, let me address your second question first. I didn't really look into it, but an explicitly declared "Document" object should have precisely the same members (properties, methods, events) that the ActiveDocument object should have. Also, if you've just got a "Document" object (let's say you've declared it as "doc1") and you need to get back to the "Word" object, all you need to do is something like:
Code:
Dim tmpWrd as Object
Set tmpWrd = doc1.Application
... and then you can just let that tmpWrd fall out of scope when you're done with it, as it'll be an extra reference to the Word application.
------------
Ok, regarding your search-and-replace question. Through the years, I've had to make several changes in that "Replace" code, and I've now got a few different versions of it. I'll post what I've got.
Actually, upon looking at it, I've got a specific BAS module for dealing with many Word automation tasks. I'll just post the entire module (see attached). Also note that I do all my automation late-bound, and that's why you'll see all those constant declarations. Also, I commented out the following procedures because they had procedure reference outside of that module: TempRndWordFileSpec, WordSetTemplate, WordIsOpen. If you need one of those, let me know and I'll work out the external procedure calls. The way it is (the attached), you should be able to drop it in as a new BAS module, and it'll work fine.
This is out of my actual production code, and has been tweaked on for 20+ years. But it's code that's exercised everyday in several locations around the U.S. & Canada, generating a plethora of reports.
To specifically address your question, take a look at the following procedures. Hopefully, one of them will get you where you need to go:
InWordReplaceFirstOccurenceInShape
InWordReplaceAllOccurencesInBody
InWordReplaceFirstOccurenceInBody
InWordReplaceFirstOccurenceInShapes
InWordReplaceFirstOccurenceInShapesEx
InWordReplaceFirstOccurenceInBodyEx
InWordReplaceFirstOccurenceInBodyOrShape
InWordReplaceAllOccurencesInAllShapesText
InWordDoTheReplace
InWordReplaceAllOccurencesInFooterAllSections
InWordReplaceAllOccurencesInFooter
InWordReplaceAllOccurencesInHeader
Also, just to say it, there's often specific work done with various "doc" objects that's just done "inline" in the code getting the work done. You won't see that work in this module.
Last edited by Elroy; Feb 6th, 2023 at 11:52 AM.
Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.
Re: Word automation started giving errors - Now have to close and re-create Word obje
Hey Elory, Your code came in very useful to check against when I modified my code to use document objects Thanks.
Can I check something with you please?
I notice that in your WordOpenDoc function, before you open a Word document, you set wrd.AutomationSecurity to msoAutomationSecurityForceDisable and then set it back after opening the document.
Even though none of my documents that I open contain macros, would you recommend I do the same? I guess this is to prevent Word displaying that pesky macro warning message box appearing when Word is told to open a document it thinks has macros?
Re: Word automation started giving errors - Now have to close and re-create Word obje
Maybe there is something else in the code that is causing the memory issue that you didn't post? Are you sure you are closing the documents in the loop before opening a new one?
Re: Word automation started giving errors - Now have to close and re-create Word obje
Originally Posted by nealb
Hey Elory, Your code came in very useful to check against when I modified my code to use document objects Thanks.
Can I check something with you please?
I notice that in your WordOpenDoc function, before you open a Word document, you set wrd.AutomationSecurity to msoAutomationSecurityForceDisable and then set it back after opening the document.
Even though none of my documents that I open contain macros, would you recommend I do the same? I guess this is to prevent Word displaying that pesky macro warning message box appearing when Word is told to open a document it thinks has macros?
Thanks.
Hi nealb,
Truth be told, that code has been working for so long (exercised many times every day by users), that I don't fully remember why I did that.
I can tell you that none of the Word templates nor Word files I use should contain any macros. And, if they do, I have no need for them to execute to get my work done. The way my code is, it just makes sure that no macros in any opened word files ever execute.
I do have cases where my users might specify a random Word file to open. And, I've got no guarantee that they didn't put a macro in one of those. However, if they open that Word file through my software, those macros will be disabled with that msoAutomationSecurityForceDisable setting.
Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.