Temporarily stop script

[H.A. de Wilde]

L.S.,

Within Excel I am running a script that opens a new Word-document.
I like to stop the script temporarily untill the Word-session is
closed.

Example:

Set objWord = CreateObject("Word.Application")
objWord.Visible = True
Set objDoc = objWord.documents.Add

Msgbox ("Excel again")

The message should appear after closing the Word-session.

Possible?
With kind regards,
Hugo

 

[Guest]

Once word is opened and the document is added, the script moves on.

You could possibly have it loop and check for the existence of Word.

 

[H.A. de Wilde]

Dear Tom,

thank you for your reply.
Unfortunately I am not able to check for the existence of Word.
Which condition should I use to exit the loop?

With kind regards,
Hugo

 

[Guest]

Without testing

Set objWord = CreateObject("Word.Application")
objWord.Visible = True
Set objDoc = objWord.documents.Add
Set objDoc = Nothing
do while objWord.Visible
loop
set objWord = Nothing
Msgbox ("Excel again")

 

[Guest]

Or, instead of using loops, why not use an event-based solution? Put the
below code in an Excel code module, call Initialize() and Terminate() as
appropriate, and you'll be able to listen to Application Quit and Document
Close events through the event handlers. I would strongly recommend the use
of event-based programming whereever and whenever possible, instead of
implementing tight loops to poll for state information, since the latter will
consume CPU cycles for virtually no good reason.

Cheers,
/MP

====================================

Option Explicit

Private WithEvents g_oApp As Word.Application
Private WithEvents g_oDoc As Word.Document

Private Sub Initialize()
Set g_oApp = CreateObject("Word.Application")
Set g_oDoc = g_oApp.Documents.Add

g_oApp.Visible = True
End Sub

Private Sub Terminate()
Set g_oDoc = Nothing
Set g_oApp = Nothing
End Sub

Private Sub g_oApp_Quit()
MsgBox "Word App Quit"
End Sub

Private Sub g_oDoc_Close()
MsgBox "Word Doc Close"
End Sub

==============================================

 

[H.A. de Wilde]

Dear Tom,

The loop works but is not working fine.
When the Word-document is minimized or there is a switch to anothe
application, the system does't perform good. Alos the return to Exce
is very slow. I think the loop is too risky. Sometimes an erro
occurs.


Dear Cheers,

Pasting the text:

Private WithEvents g_oApp As Word.Application
Private WithEvents g_oDoc As Word.Document

into a module, it turns into red.

Thanks for your replies.

Kind regards,
Hug

 

[Guest]

Dear Tom,

The loop works but is not working fine.
When the Word-document is minimized or there is a switch to another
application, the system does't perform good. Alos the return to Excel
is very slow. I think the loop is too risky. Sometimes an error
occurs.


Dear Cheers,

Pasting the text:

Private WithEvents g_oApp As Word.Application
Private WithEvents g_oDoc As Word.Document

into a module, it turns into red.

Thanks for your replies.

Kind regards,
Hugo

 

[Guest]

In order to use the code fragments I put together you have to properly add a
reference to the type library of MS Word; this is because I was using the
early-bound types Word.Application and Word.Document to declare the global
variables.

Hence, in the VBA editor, go to Tools > References, and in the References
dialogue add a reference to the Microsoft Word Object Library.

You can then paste the two global variable declarations (g_oApp and g_oDoc)
into the General Declarations section of, for example, the ThisWorkbook code
module.

HTH,
/MP

 

[Guest]

I think Mat's concept is a much better one. Perhaps he will share with you
how to implement it.

 

[NickHK]

Hugo,
In a class module:
Dim WithEvents MyWord As Word.Application

Private Sub MyWord_Quit()
MsgBox "Excel again"
End Sub

Your original sub would need to end before then original MsgBox.
But you will then be notified when Word quits.

NickHK

"H.A. de Wilde" <[email protected]>
wrote in message
news:[email protected]...