Closing word from excel

[PatrickS]

One spreadsheet I'm on has textboxes on each sheet containing comments aimed
at the user, however sometimes the comments they want to enter are to big to
be printed, so I've ended up opening word from Excel in such circumstances,
then copying the text of the comments to the document. It then saves the
document and closes word and also stops the textboxes from being included in
any printout. I also have error handling that attempts to properly close word
and exit gracefully.

However I've noticed that when an error handling code is executed word asks
me if I want to save the file. I'm aware that word has the DisplayAlerts
property but uses different values from excel, and have added the pre-defined
constants to my code so that I could use pre-existing code without having to
go through and make corrections. The following two lines of code should, I
believe, end up with alerts in words disabled from excel. However this only
works when I step through the code. Running the code normally results in the
save alert still being displayed. Is there any reason for this?

WD.DisplayAlerts = wdAlertsNone
WD.Quit

Thanks for any help in advance.

 

[Andy Williams]

What is the exact error message you are getting?

Could you post the full code of the routine.

Regards

Andy W

 

[PatrickS]

The document that Word is trying to open is already open, which causes an
error to be raised (I had previously opened the document that had been
generated then tried to generate another using the same file name).

 

[Andy Williams]

I'm getting confused!

You're first post implied you were getting a Save As alert error when you
used WD.Quit.

You're second post implies that you are getting a File Already Open error.

Anyway, following your first post, I've tried the code below which opens a
Word doc, adds a brief bit of text, saves it and then quits word (after
turning the alerts off).

This works for me without giving any Save As alerts. I'm not sure it's what
you are after but I'll post it anyway in the hope that is helps.

Sub Macro1()
' Macro1 Macro
' Keyboard Shortcut: Ctrl+r
Dim oWD As Word.Application
Dim oDoc As Word.Document

Set oWD = CreateObject("Word.Application")
oWD.Visible = True
Set oDoc = oWD.Documents.Add
oDoc.Content = "Hello " & Now()
oDoc.SaveAs "Trevor " & Format(Now(), "yyyymmddhhmmss") & ".doc"
oWD.DisplayAlerts = wdAlertsNone
oWD.Quit

End Sub

Regards

Andy W

 

[PatrickS]

It was an alert, but not in itself an error per se, rather it was a result of
an error occurring elsewhere in the code.

Under normal circumstances it would just write what it needs to do to the
word document, then save and close it. This works fine. The document has just
been saved so no alert is produced asking me if I want to save the document a
second time.

However when an error occurs it first displays an error message (a
predefined message box that I have added) and then attempts to close things
gracefully - including closing word - and it is at this point that it
displays the question asking me if I want to save. The error that was
occurring that prompted this was the one I described in the second post.

I'm happy at this point with the way it handles the error generally
speaking, but would prefer to not see the question in regards to saving the
document, since from the user's point of view there would be nothing to save.

The problem I have is that the constant that both of us have used appears to
have no effect. Word keeps on asking me if I want to save regardless of
whether I try to turn alerts off in word or not.

Thanks for your help by the way.

 

[Andy Williams]

Ok, now I understand.

The process as I understand it is as follows:-

1) You get an error which you have a routine to handle

2) because of the error the changes have made to the Word document have not
been saved

3) because the changes haven't been saved word gives you the Save As dialog

The only thing I can think of is in handling the error you undo any changes
that have been made to the Word document so that when you Quit there isn't
anything to save and so it doesn't need to put the Save As dialog up.

Something like this:-

Sub Macro1()
' Macro1 Macro
' Keyboard Shortcut: Ctrl+r
Dim oWD As Word.Application
Dim oDoc As Word.Document

On Error GoTo ErrHand

Set oWD = CreateObject("Word.Application")
oWD.Visible = True
Set oDoc = oWD.Documents.Add
oDoc.Content = "Hello " & Now()
'the next line contains an invalid path which forces an error
oDoc.SaveAs "d:\not there\Trevor " & Format(Now(), "yyyymmddhhmmss") &
".doc"
oWD.DisplayAlerts = wdAlertsNone
oWD.Quit

ErrHand:
MsgBox "An Error has occured"
GoTo ErrQuit

ErrQuit:
oWD.DisplayAlerts = wdAlertsNone
oDoc.Undo
oWD.Quit

End Sub

I'm suspect there's a more elegant way but I can't think of it at the moment

 

[Andy Williams]

Got it, I knew there was a more elegant way! You need to use:-

oWD.Quit wdDoNotSaveChanges

which forces Word to Quit without trying to save the changes.

HTH

Andy W