Results 1 to 17 of 17

Thread: Word automation started giving errors - Now have to close and re-create Word object

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Apr 2021
    Posts
    27

    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.
    ***
    Last edited by nealb; Feb 3rd, 2023 at 11:27 AM.

  2. #2
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,872

    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.

  3. #3
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,853

    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.

  4. #4

    Thread Starter
    Junior Member
    Join Date
    Apr 2021
    Posts
    27

    Re: Word automation started giving errors - Now have to close and re-create Word obje

    Quote Originally Posted by Arnoutdv View Post
    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.

  5. #5
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,853

    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.

  6. #6

    Thread Starter
    Junior Member
    Join Date
    Apr 2021
    Posts
    27

    Re: Word automation started giving errors - Now have to close and re-create Word obje

    Quote Originally Posted by Elroy View Post
    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.

  7. #7
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,853

    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.

  8. #8

    Thread Starter
    Junior Member
    Join Date
    Apr 2021
    Posts
    27

    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?

  9. #9
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    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.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  10. #10
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,853

    Re: Word automation started giving errors - Now have to close and re-create Word obje

    Quote Originally Posted by nealb View Post
    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.

  11. #11

    Thread Starter
    Junior Member
    Join Date
    Apr 2021
    Posts
    27

    Re: Word automation started giving errors - Now have to close and re-create Word obje

    Quote Originally Posted by Elroy View Post
    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.

    Many thanks.

  12. #12
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,853

    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.
    Attached Files Attached Files
    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.

  13. #13

    Thread Starter
    Junior Member
    Join Date
    Apr 2021
    Posts
    27

    Re: Word automation started giving errors - Now have to close and re-create Word obje

    Thanks very much Elroy. Hugely appreciated. I will have a go at changing my code to work with a document object......

  14. #14

    Thread Starter
    Junior Member
    Join Date
    Apr 2021
    Posts
    27

    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?

    Thanks.
    Last edited by nealb; Mar 7th, 2023 at 12:06 PM.

  15. #15
    PowerPoster
    Join Date
    Feb 2017
    Posts
    4,997

    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?

    On another hand, I found that creating the Word object in newer Office versions is very slow.

  16. #16
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,853

    Re: Word automation started giving errors - Now have to close and re-create Word obje

    Quote Originally Posted by nealb View Post
    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.

  17. #17

    Thread Starter
    Junior Member
    Join Date
    Apr 2021
    Posts
    27

    Re: Word automation started giving errors - Now have to close and re-create Word obje

    Thanks Elroy. Makes sense. I think I'll do the same.
    Neal

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width