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

[Jenn68]

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.

[Jenn68]

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,

[AliGW]

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!

[Jenn68]

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

[AliGW]

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.

[Jenn68]

Cross posted here ...
https://stackoverflow.com/questions/...eased-by-excel

[macropod]

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.

[Jenn68]

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.

[dmcgov]

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.

[Jenn68]

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?

[dmcgov]

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

[Jenn68]

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).

[macropod]

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

[Jenn68]

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.

[Jenn68]

Can I

bump

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

[macropod]

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.

[Jenn68]

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.