Working with Multiple Appls Simultaneously

[MWE]

Assume two differnt MS applications, e.g., Word and Excel. I have a procedure that runs in Word that creates an Excel object, opens a target xls file, does things to that file, closes/saves it and then sets all objects to nothing. Simple enough.

Suppose I want the user to be able to "do things" with the open xls file? So now the Word proc creates an Excel object, opens a target xls file, transfers "control" to the user, the user fiddles with the xls file, control is passed back to the word proc, the word proc closes/saves the xls file, and then sets all objects to nothing. How might I do that?

One method that almost does everything is to hyperlink from word to the xls file. That nicely halts word, opens the xls file in a new window and allows the user to fiddle as much as he/she wants. The problem is that there is no guarantee that the user will properly close/save the xls file The other problem is that word and excel are examples of what I want to do. The more general case is any MS appl to any MS appl. Some appls do not support hyperlinking, e.g., Outlook (I think)

[mdmackillop]

Life would be simple wihout Users!
You can add a BeforeClose or BeforeSave event to close/save "properly" and return to Word.

[MWE]

Life would be simple wihout Users!
You can add a BeforeClose or BeforeSave event to close/save "properly" and return to Word.Thanks for the reply but I do not undertand the utility of your suggestion. Where do I add the BeforeClose or BeforeSave event? And how does that resolve the problem associated with appls that do not support hyperlinking?

[gwkenny]

1) The problem with hyperlinking is that you lose control.

2) Starting Excel from Word and then exiting your macros is better, but you still lose control. It is better because you can use code to set the environment of the Excel object you just started (for example, not giving the user menu options like file save as, etc...), but Users will usually find a way to screw that up too :)

3) The next best solution I've found is to use a modeless form. Word brings up Excel. Sets up Excel to acquire User input, then puts up a modelss form so the User can press OK or what have you when the User is finished with the Excel object. The modeless form allows continued execution of code, so you need to put something in the code to pause it while the User is fiddling with the Excel object.

Do Until frm_MyModelessFormToTellIfTheGooberUserIsFinishedWithExcelObject.Visible = False
DoEvents
Loop

You can also expand the Do Until criteria to include if the Excel Object you defined is still running.

The problem with this is that it does make your machine less stable, so only code this way if the computers have lots of memory.

[Dave]

Just thought that I would add that you could use VB6 to create and control all your MS apps. Not sure if that helps but it seems relevent. Dave