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]

Hi Anna,

It depends a little on how you are displaying the dialog but the Show Method returns True or False depending on whether or not the User canceled, perhaps something like ..

[blue]If Application.Dialogs(wdDialogFileSaveAs).Show = False Then MsgBox "SaveAs Canceled"[/blue]

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]

I am doing this now:

FileSaveName = Application.GetSaveAsFilename(InitialFileName:="", Filefilter:="Word Files (*.doc), *.doc")
If FileSaveName <> False Then
With Mydoc
.SaveAs Filename:=FileSaveName, FileFormat:=wdFormatDocument, ReadOnlyRecommended:=True
.Close SaveChanges:=False
End With
DocSaved = 1
Else
DocSaved = 0
GoTo errSavingFile

End If

....
then:
errSavingFile:
If DocSaved = 0 Then
Resume
Else
DocSaved = 1
End If

... then:
err_Populate_Doc:
If Err <> 0 Then
If ErrorDocName = True Then
MsgBox "A contract was not selected to populate!"
Populate_Doc = 1
Else
MsgBox "Error in Populate_Output. You might need to delete WINWORD from Task Manager" & Err & ":" & Error(Err), vbCritical
Populate_Doc = Err
Set Mydoc = Nothing
'Set WordApp = Nothing
WordApp.Quit
End If
End If

-----------------

Now, this seems to be working but is it the best way to do this??

Also, I need to open the document at the end.. how do I do this?
I tried: documents.open(mydoc)
but it errored out.. something about the client not being available..
so, I need to know when I have to do this..
I tried it at a few locations but no luck!!

Thanx soooo much for your help,
Anna

 

[TonyJollans]

Hi Anna,

Let's see if I understand this.

1. You create an instance of Word (from Excel)

2. You open two new documents - one based on template mydoc and the other based on template mydoc2 - let's ignore the second one for the moment.

3. You prompt the user for a filename

4. If you get a name from the user then
a) you save the "mydoc" document under that name
b) you close the "mydoc" document
c) you want to reopen the same document

5. If you don't get a name from the user (ie they press Cancel) then
a) you discard the document?
b) and then you do what?

This all seems a bit complicated. Can you answer the questions under point 5, and confirm you want to reopen the document under point 4 (and, if so, say why you want to close it to begin with).

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[

 

[PHV]

documents.open(mydoc)
1) Use a Word.Application object when you play with the Documents collection.
2) Reading your post, mydoc seems to be a Word.Document object. Use a pathname string instead.

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

 

[Anna007]

Hi Tony,

Thank you for your time.

Ok. I'll try and answer the questions as much as I can...

1-2. lets just says there is one instance of a word doc (I have a template that I have book-marked and populate via Excel).
3. After populated, I prompt the user (explorer window) for a name for the SaveAs name.
4. If I get a name from the user, I save this document where they decided to save it from the explorer. I close mydoc here too. At some point, after the task of populating book-marks, I need to open the file for the user. So, I don't need to close it. I don't know how to do this. Do I save it and then keep it open??!
I tried documents.open(mydoc) at 3 different locations and got errros. Once it complained for overflow. So, now I don't know where to put this line of code. Do I do it after saved or before then?

5. If they don't provide a name and hit cancel, I just resume and capture the error (as u can see above in the code). Well, I want to see what else I can do here... this part seems to be working fine.

I have my deadline on friday and I'm really trying to get this figured out!

Hope this has been detailed enough..

Thanx for your help,
Anna

 

[PHV]

Anna, have you understood my remarks ?

 

[TonyJollans]

Hi Anna,

Firstly, your immediate problem.

You have:
- WordApp, a pointer to a Word session (instantiated from Excel), and
- MyDoc, a pointer to an Open Document within the Word session

When you close your document the pointer, MyDoc, becomes invalid. When you try to use it again you get some sort of message to that effect. It is, however, the wrong thing to be using so the actual explanation of the error is academic. The syntax to open a document is:

[blue][tt] WordApp.Documents.Open([red]Document_Name[/red])[/tt][/blue]

In your case you should use:
[blue][tt] WordApp.Documents.Open([red]FileSaveName[/red])[/tt][/blue]
instead of:
[blue][tt] Documents.Open(MyDoc)[/tt][/blue]

Now, ignoring mydoc2, ..

.. you ask the User for a filename (using Excel facilities) and save the document if a name is supplied. Whether or not you save, you then want to continue working with the document.

Two things:

1. Your process would be simpler if just threw the SaveAs Dialog direct to the User
2. There is no benefit in closing and reopening the document

This should be a little easier for you:

[blue]
Dim DocSaved As Boolean
DocSaved = WordApp.Dialogs(wdDialogFileSaveAs).Show
If DocSaved Then
    [green]' Do whatever you want next with the document[/green]
Else 
    [green]' Do whatever you want next with the document here as well![/green]
End If 
[/blue]

I'm afraid I don't understand your 'error' logic. You jump to errSavingFile if the User Cancels and do nothing obviously useful. And you don't show how you get to err_Populate_Doc so I'm not sure what you have or what you need at that point.

I'm not sure how helpful that is as I'm not entirely sure what your objective is. As a final note you might find it helpful to [blue]Set WordApp.Visible = True[/blue] both as an aid in testing and also, perhaps, in an error situation rather than suggesting the User go to the Task Manager.

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 Tony,

Thank you soooooo much for all your help. Very nice of you!!
Ok. Here it is.. I tried the line of code you gave me for opening the document after I close it:
WordApp.Documents.Open(FileSaveName)

The problem is very strange..
If I put a breakpoint on this line, then, it'll open the file but will not display it. I tried activate and it still doesn't maximize it. it just has it minimized. But if I don't put a breakpoint there on that line, it won't do this. It just ignores it or something.. it doesn't open the file. I don't know why!!

Any ideas??

Thanx,
Anna

 

[Anna007]

I have another problem.. in the task manager, I have sooo many Winword.exe processes .. as I've been testing.. so, what do I do with them???

Thanx,
Anita

 

[TonyJollans]

Hi Anna,

Got to be real quik at the moment.

Kill all your Word proceses via Task Manager then try again - and make sure you terminate each as you go along - if your procedure errors out before finishing properly go and kill the task - and use Set Wordapp.Visible = True while testing so you can see what's going on.

I will check back in a few hours.

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[

 

[PHV]

You have so many winword.exe because you use word objects not instantiated in your current Word.Application.
Reread my first reply.

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

 

[fumei]

Anna, while having Tony on the case here is like having a patron saint...oh never mind..

Anyway, following through this post, I am not sure you quite read PH's post, but more importantly, could you clearly state what your goal is?

1. You state in your original post that you are

openings mydoc and mydoc2 (which are templates)

First of all, are you opening REAL templates, that is, are they .DOT files? Or are they .DOC files? If they are .DOC files, then they are not really templates.

2. If you want to use a real template properly then you create a NEW document, and attach the template to it. This may help solve your problem, because a new document created from a template does not have a name. Therefore there is no need for SaveAs, just use Save.

3. If you are using a document, not a real template, then if you know the folder the file should be saved in, then just use an InputBox to get the name the user wants to save it as, then YOU save the file directly. Or in your process of populating from Excel require a filename. It would be simple to do. Have a textbox on a form, and if it is blankl, or the user cancels out, either give a message, or shut the whole thing down.

Again, could you clearly state what your goal is? Does your process get actual user input? If so, require a filename as part of that input. If you use a real template then opening an instance of Word can skip the Open process. You can use AttachedTemplate instead, then save it.

If the process does NOT require any user input other than a new filename, display a userform with only an OK button, a textbox for input, and make Query_Close (which affects the "x" close icon) also require input into the textbox. That way, they have to put something. You take that something and save the file with it. End of story.

Unless you want to do further error trapping for the filename, even the process you are working on now will accept a SaveAs with gibberish. If the user puts in "fghskdotkfjsj.doc" it is going to be saved that way.

However, again, it would be better to use a real template.

Gerry

 

[Anna007]

I don't know how to Terminate the Processes via VBA code... don't know the command for it.. Does wordApp.quit work for this??

I tried that line of code: Set Wordapp.Visible = True but gave me an errror.

Now as for the main problem here:

Ok. Here it is.. I tried the line of code you gave me for opening the document after I close it:
WordApp.Documents.Open(FileSaveName)

The problem is very strange..
If I put a breakpoint on this line, then, it'll open the file but will not display it. I tried activate and it still doesn't maximize it. it just has it minimized. But if I don't put a breakpoint there on that line, it won't do this. It just ignores it or something.. it doesn't open the file. I don't know why!!

So, is it the timing?? I guess I have to make it wait for some time??? how do I do this??


Thanx,
Anita

 

[Anna007]

Hi PHV and Gerry,

I have read your comments.. thank you ..
The book-marked documents are in .doc format. I don't want these files touched.. what I want is to populate them and then, save them as something else.. however, the problem is if the user clicks on cancel on the SaveAs Explorer Window, these docs are populated with Excel data already and then, wodapp.quit prompts the user if he/she wants to save the changes made to the orginal document.. so, the answer would have to be NO. I don't want any changes made to the original documents.. this is the issue!
So, what do I do here???
I still don't know the command to terminate winword.exe!

Thanx so much,
Anna

 

[PHV]

To avoid the save changes prompt, simply do this before the SaveAs:
MyDoc.Saved = True

BTW, use Wordapp.Visible = True (without Set)

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

 

[Anna007]

Hi,

Have another question:
I have this code for when the user needs to select the file from where a directory.
I need it to default to where the workbook is saved and not just temp to begin with:
Now, as soon as the dialogue is displayed, the user needs to go to the appropriate directory to select the file. Like to make it easier ..

If Dir(DocumentName) = "" Then
MsgBox "You will need to select the appropriate file!", vbExclamation
fileToOpen = Application.GetOpenFilename(Filefilter:="Word Files (*.doc), *.doc")
DocumentName = fileToOpen
If fileToOpen = False Then
ErrResponse = MsgBox("Invalid path or file name specified.", vbCritical, "Name")
Exit Function
End If
End If

Thanx,
Anna

 

[PHV]

Have you tried to play with the ChDir function ?

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

 

[Anna007]

Ok. I already played around with Chdir and it worked well. So, I've got that figured out.. thanx however..

Now, I just need to know how to terminate a process (winword.exe from the task manager). Would wordapp.quit do it??

Thanx for all of your help everyone..
Hope I don't run into any big problems tomorrow..

Anna

 

[PHV]

Provided you don't have any implicit instantiation of word (like using Documents instead of wordapp.Documents ...) a simple call to wordapp.Quit followed by Set wordapp To Nothing should suffice.

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