+ Reply to Thread
Results 1 to 17 of 17

Mail Merged Documents Created By Excel Macro Not Being Released By Excel?

  1. #1
    Forum Contributor
    Join Date
    06-15-2009
    Location
    Ontario, canada
    MS-Off Ver
    Office 365
    Posts
    335

    Mail Merged Documents Created By Excel Macro Not Being Released By Excel?

    I have a unique problem with my Excel VBA based application.

    Let me set the stage ...
    I have a userform with a series of toggle buttons, each one representing a different report option, in my userform. When the user selects one (or more) reports by pressing that report's associated button that report query is put into a cue for which the code will loop through to process each.
    Within the loop, a Word mail merge is executed for that particular report. The mail merge is executed, the document created, and subsequently saved. Those newly created Word documents remain open. The preference is for the user to close those word documents by clicking the previously depressed toggle button corresponding to that particular report in the userform. This appears to be working OK.

    Now the user has the ability to access these documents once created (and before being closed) and edit and print them. They print fine, but they are unable to save any changes. The user can click the save icon in the menu of the open word document until their mouse batteries go dead and nothing saves. However, if you go back to Excel, click on the worksheet, and then go back to the Word document, you can save it. (almost like doing so broke a tether between the two docs)

    If the user accesses these Word documents from the location they were saved (ie from the directory) from a closed state, they open, and can be edited, but again, cannot be saved. That is as long as Excel is open to the application that created the docs. EDIT: This is not the case. Even with EXCEL closed, these previosuly created mail merged documents, although editable editable. cannot be resaved due to file permission errors.

    What I have discovered is, if my Excel application is closed, these files no longer exhibit the difficulties saving due to file permission errors. It would appear that once Excel has initiated and generated these reports, that it has some "lock" on them. These documents, as long as the Excel application used to generate them is open, they appear not to be fully released from the Excel generation code.

    Here is the entire macro I use to do all of this. Perhaps someone is able to share with me what the problem might be and hopefully a relatively simple solution for this analysis.

    Please Login or Register  to view this content.
    Thank you all in advance.
    Last edited by Jenn68; 05-31-2019 at 06:32 AM. Reason: Text in purple added.

  2. #2
    Forum Contributor
    Join Date
    06-15-2009
    Location
    Ontario, canada
    MS-Off Ver
    Office 365
    Posts
    335

    Re: Mail Merged Documents Created By Excel Macro Not Being Released By Excel?

    As this seems to have stumped the pros here, is it ok if I post this elsewhere this elsewhere and provide that link here?
    I've added a couple edits as a result of testing since the original post,
    Last edited by Jenn68; 05-31-2019 at 06:34 AM. Reason: Highlighted edits to OP.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,108

    Re: Mail Merged Documents Created By Excel Macro Not Being Released By Excel?

    Yes - see forum rule #3.

    You haven't bumped your thread at all (we'd expect you to do so once a day if you've had no responses). I imagine it's gone unnoticed, rather than having stumped anyone!
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  4. #4
    Forum Contributor
    Join Date
    06-15-2009
    Location
    Ontario, canada
    MS-Off Ver
    Office 365
    Posts
    335

    Re: Mail Merged Documents Created By Excel Macro Not Being Released By Excel?

    Thank you Ali. I did't think bumping was good etiquette.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,108

    Re: Mail Merged Documents Created By Excel Macro Not Being Released By Excel?

    Bumping is fine, as long as it's no more than once a day. What is not good etiquette is bumping multiple times a day. Hopefully someone with the requisite VBA skills will now see the thread. There are a few online at the moment.

  6. #6
    Forum Contributor
    Join Date
    06-15-2009
    Location
    Ontario, canada
    MS-Off Ver
    Office 365
    Posts
    335

    Re: Mail Merged Documents Created By Excel Macro Not Being Released By Excel?


  7. #7
    Forum Guru macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,553

    Re: Mail Merged Documents Created By Excel Macro Not Being Released By Excel?

    The problem is that you're creating a new Word session to generate the documents, but you never close them or exit the new Word session, leaving it orphaned in the background with the document still open.
    Cheers,
    Paul Edstein
    [Fmr MS MVP - Word]

  8. #8
    Forum Contributor
    Join Date
    06-15-2009
    Location
    Ontario, canada
    MS-Off Ver
    Office 365
    Posts
    335

    Re: Mail Merged Documents Created By Excel Macro Not Being Released By Excel?

    Hi Paul!

    I gathered something that, but I'm not sure where or how to do that.
    I have two instances of .close in my code (purple) figuring that was what they were doing.

    Please Login or Register  to view this content.
    I'm not even sure how to Google that for answers.

  9. #9
    Valued Forum Contributor dmcgov's Avatar
    Join Date
    11-11-2015
    Location
    Florida, USA
    MS-Off Ver
    Office 365 Business
    Posts
    1,411

    Re: Mail Merged Documents Created By Excel Macro Not Being Released By Excel?

    so Jenn68, is the problem with the word document not closing (or any word doc)? if so i found this code on the web.

    Please Login or Register  to view this content.
    let me know

    Note: Nevermind, i tried it and got error. i will try to debug.

    so found this code on the web and it closes at instances of word, no matter how many are open

    Please Login or Register  to view this content.
    let me know if you can make that work for you.
    Last edited by dmcgov; 05-31-2019 at 10:14 AM.

  10. #10
    Forum Contributor
    Join Date
    06-15-2009
    Location
    Ontario, canada
    MS-Off Ver
    Office 365
    Posts
    335

    Re: Mail Merged Documents Created By Excel Macro Not Being Released By Excel?

    Thank you dmcgov for scoping things out for me.
    At what point would I have to put the 'if TaskKill' statement in my code? After the first or second instance of close?

  11. #11
    Valued Forum Contributor dmcgov's Avatar
    Join Date
    11-11-2015
    Location
    Florida, USA
    MS-Off Ver
    Office 365 Business
    Posts
    1,411

    Re: Mail Merged Documents Created By Excel Macro Not Being Released By Excel?

    second, do it as the last thing in the macro. good luck.

  12. #12
    Forum Contributor
    Join Date
    06-15-2009
    Location
    Ontario, canada
    MS-Off Ver
    Office 365
    Posts
    335

    Re: Mail Merged Documents Created By Excel Macro Not Being Released By Excel?

    So far so good!! I will have to do more testing to know for sure, which includes taking it to another computer (I have many problems associated to using different computers).

  13. #13
    Forum Guru macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,553

    Re: Mail Merged Documents Created By Excel Macro Not Being Released By Excel?

    As you say, you had the .Close statement for your output document commented-out. And, instead of crashing out of Word via dmcgov's code, which leaves all manner of trash about the place, you should end it properly:
    objWord.Quit
    Set objWord = Nothing

  14. #14
    Forum Contributor
    Join Date
    06-15-2009
    Location
    Ontario, canada
    MS-Off Ver
    Office 365
    Posts
    335

    Re: Mail Merged Documents Created By Excel Macro Not Being Released By Excel?

    Along the same line, I'm running into the same problem when I open / close a report created by the previous code.
    A user can view a specific report previously completed by engaging a toggle button. The resulting Word document that is opened by this code can be viewed, edited, but not saved after edits made. I have commented out lines of code that I tried to apply Paul's suggestion.

    Please Login or Register  to view this content.
    The area in purple is where I think I need improvement.

  15. #15
    Forum Contributor
    Join Date
    06-15-2009
    Location
    Ontario, canada
    MS-Off Ver
    Office 365
    Posts
    335

    Re: Mail Merged Documents Created By Excel Macro Not Being Released By Excel?

    Can I

    bump

    this with the minimum 10 characters.
    This is causing me grief.

  16. #16
    Forum Guru macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,553

    Re: Mail Merged Documents Created By Excel Macro Not Being Released By Excel?

    Post #14 seems to deal with an entirely separate problem, for which you should start a new thread. And, since the issue concerns Word, I'd suggest you start that thread in the Word programming forum.

  17. #17
    Forum Contributor
    Join Date
    06-15-2009
    Location
    Ontario, canada
    MS-Off Ver
    Office 365
    Posts
    335

    Re: Mail Merged Documents Created By Excel Macro Not Being Released By Excel?

    Thank you Paul, I will repost (fearing getting blasted for a duplicate post though). I might be able to better explain what's happening in a new post

    I did feel they are the same problem ... file permission errors resulting from perhaps not closing or exiting the new Word session after a document had been created from opening via Excel macro. The only difference is that in the OP I was having the problem immediately after the creation of the file, but in the latest case it happens from opening the already created and stored document from within the same VBA code. I kept this thread flowing here simply because the code is in Excel and launches a Word feature.

    Standby.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Mail Merge Excel Info into Multiple Different Word Documents
    By hilarymorton2003 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-29-2016, 12:28 AM
  2. [SOLVED] Documents created from mail merge won't print
    By mgs73 in forum Word Formatting & General
    Replies: 7
    Last Post: 02-28-2016, 08:05 PM
  3. Excel/Word Mail Merge, saving as individual documents using two mergefields as name
    By flakjack in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-01-2016, 12:34 PM
  4. convert all 97-2003 excel documents to macro enabled documents on server
    By viperisback in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-23-2013, 09:06 AM
  5. Excel 2007 : excel data when mail merged displays wrongly
    By johnmaria09 in forum Excel General
    Replies: 0
    Last Post: 11-28-2008, 01:55 PM
  6. [SOLVED] Create merged mail in Excel (*NOT* in Word)?
    By DolfnJudy in forum Excel General
    Replies: 0
    Last Post: 11-08-2005, 09:40 AM
  7. [SOLVED] how do I use mail merge in excel for word documents
    By Lisa J Duffy in forum Excel General
    Replies: 1
    Last Post: 08-04-2005, 09:05 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1