Need help with OnCancel syntax in VBA when working with docs

[Anna007]

Hi,

I am openings mydoc and mydoc2 (which are templates) and populating them via Excel automatically (via vba code). Now, I display the explorer window for the user to provide a SaveAs name at the end. If I hit cancel, it just continues. I don't want this. I want it to stop and set the docs to nothing.
I'm desparately in need of help..
Now, at the end.. I'm thinking of opening the document for the user.. after saved. how do I do this? Documents.open()???

Thanx,
Anita

 

[TonyJollans]

Good Morning All,

Firstly thanks to Gerry for more of his kind words, and to both Gerry and PH for picking up while I went out for a drink (and came back unfit to post), and thanks to PH for pointing out my repeated error about using Set with .Visible.

Now, Anita, Gerry is correct in that you should be using Templates (.dot files) instead of skeleton documents (.doc files) and you should follow PH's advice and make sure you properly qualify all Word objects with the Word Application (however I don't think you can have implicit instantiation by not doing this).

That aside your procedure doesn't seem to be particularly complex but I am confused about a couple of things. The process seems to involve prompting the user for some file names, opening and populating a Word document, and then giving the user the option to save and possibly presenting the user with the document for further editing.

1. I presume that there is something else going on (or why would you be doing this from Excel).

2. Why do you do all the work and then allow the user to throw it away?

Lastly, if you are allowing the user to edit the document after you have done, this could be causing some of your problems. My understanding of what happens is this:

- You instantiate Word from Excel and you maintain a pointer to it (WordApp). The Word object will exist as long as that pointer is maintained.

- You hand over the Word Application to your User. The User does what they want - and, then, presumably, closes Word (is this correct?)

- After the User has closed Word the Window is closed BUT the Word object remains in memory (and in the Task List) because you still have a pointer to it, WordApp, in your code.

I would need to run some tests to confirm this but I offer it as possible explanation.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[Anna007]

Hi to all of you,

You are all very nice to help me out so much... I really appreciate it!!
I can work to improve this a little more till next week friday.. but not much time left at all!
So, I will use WordApp.quit as I have been using it. I wasn't sure if this terminates the process or not.

Tony,
This is all part of the requirements.. I can't get into the details of why we decided to go this route..
However, you have it right.. as you explained.
So, at the end, after I've saved the documents that were populated, I open them and make them visible to the user by:

WordApp.Documents.Open (FileSaveName)
WordApp.Visible = True

This opens the doc and has it minimized so that they can just view it or print it .. this is all for convenience.

So, does this mean that these 2 lines of code keep the process of WinWord.exe in the task Manager? So, if the user closes them after viewing them, they won't get terminated??
So,what do I do then, if they don't get terminated here?
I can't do wordapp.quit after these 2 lines since it would just close it all.. righ??
So, how do I deal with this..
The requirement is to save the file and then, open it so that the user doesn't have to go and look for it where it was saved.. to make it easier on them.

Thanx sooooooooo much,
Anna

 

[PHV]

Don't forget this:
Set WordApp = Nothing

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TonyJollans]

Hi Anna,

You could try ..

WordApp.Documents.Open (FileSaveName)
WordApp.Visible = True
Set WordApp = Nothing

.. without quitting. Setting (and this does need Set!) the WordApp pointer to nothing means you lose contact with it from your code but it does not close the application - it just leaves it visible for the user and when they close it, it should go.

You really need to test to see what happens. I had a little play earlier and I couldn't get it to happen at all, and I have a suspicion that this may be version related.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[fumei]

The requirement is to save the file and then, open it so that the user doesn't have to go and look for it where it was saved.. to make it easier on them.

Pardon me, and I do not mean to be rude, but this is a silly requirement. For one thing there is this wee point between...."save the file"...and "open it" that is missing. It is called "closing". Why on earth would you save the file, close it, and then immediately open it again? And if you are not doing that, then you are not "opening" it again. If you are using FileSaveAs then the visible (current) file IS a different one from the one you started with - why close and reopen? The user needs to see the fiel...it is right there in front of them. I am missing something...not uncommon really. But could explain what you are trying to make "easy" for the user, when the file is right there?

Again, if you want to maintain an original, and do not let the users mess around with it - in other words if a requirements is to build a COPY of something, then use a real template. Word is totally designed for using templates. Making a copy of a document then saving in as another name is NOT using a template.

Gerry