Interaction between Excel and Word

[MWE]

I have an Excel appl that needs to "operate" on a word doc. If the "operation" is simply to open/display a word file, and let the user do whatever they want with the word file, then a simply hyperlink works fine. If no user interaction is required, then using the CreateObject("Word.Application") approach and directly operating of the word doc works well. But in this case, I wish to do both and have the Excel appl "know" what has happened. The process should be something like:

1. Excel appl interacts with user to identify target word doc
2. Excel appl opens the word doc
3. user selects a target paragraph in the word doc
4. Excel appl "fetches" that paragraph and does unnatural things to it
5. Excell appl close the word doc with no changes
6. Excel appl does unnatural things to the fetched text

Items 1, 2, 5 and 6 are pretty simple. The difficult part for me is #3 and #4, i.e., how to "switch control" to the Word instance, let the user do what is required, switch control back to the Excel appl and post the paragraph text to a variable in the Excel appl. I am sure that there is some simple way to do this ... so what is it?

Thanks

[Dave]

This link may be useful. XL finds a word in a paragraph and then expands the variable to include the whole para. As far as doing "unnatural things" that depends upon your definition. HTH Dave
http://www.mrexcel.com/board2/viewto...36269&start=10

[TonyJollans]

If the user ever gets control you're going to have problems - isn't it always that way?

So you must make sure that everything runs under VBA control. You must have code running (or waiting to run) while the user is interacting with the Word document. You can do this by having a non-modal UserForm (needs at least Word 2000) in Word. It doesn't need to be complex - just a message saying "Select Paragraph and Click OK" and an OK button, or similar. When the user presses OK, you can save the paragraph for later retrieval in Excel.

In order that you return to Excel from Word, the Word code must be driven from Excel - use Application.Run for this.

This is just off the top of my head - I haven't tried this but as I write I wonder if there's a potential KB entry hiding here.

[MWE]

This link may be useful. XL finds a word in a paragraph and then expands the variable to include the whole para. As far as doing "unnatural things" that depends upon your definition. HTH Dave
http://www.mrexcel.com/board2/viewto...36269&start=10

Thanks. I will check out this link and get back to this thread (one way or another).

I have always enjoyed the adjective "unnatural" ever since I saw/heard it used in a BBC TV drama 10 or so years ago based on the RF Delderfield book(s) "To Serve Them All My Days". When suggesting that some of the boys at a British public school might be involved in "gay" activities, the headmaster used the term "unnatural practices". What a wonderful way to put it!

UPDATE: I looked at the mrexcel item above. It does a lot of work with a word doc, but all information about what it is supposed to do that is provided by the user is gathered via Excel before the word doc is processed. I know how to do that. What I want to do is a little different. I want the Excel appl to open the word doc (that's easy) and then let the user navigate around in the word doc, select some text and then "tell Excel" that the selection process is complete (that's the hard part).

Thanks

[MWE]

If the user ever gets control you're going to have problems - isn't it always that way?

So you must make sure that everything runs under VBA control. You must have code running (or waiting to run) while the user is interacting with the Word document. You can do this by having a non-modal UserForm (needs at least Word 2000) in Word. It doesn't need to be complex - just a message saying "Select Paragraph and Click OK" and an OK button, or similar. When the user presses OK, you can save the paragraph for later retrieval in Excel.

In order that you return to Excel from Word, the Word code must be driven from Excel - use Application.Run for this.

This is just off the top of my head - I haven't tried this but as I write I wonder if there's a potential KB entry hiding here.

Thanks: I have used non-modal UserForms previously when I wanted the form to stay on the screen while the user did other things. To date, that was all within a single environment. Using it in this multiple appl environment makes a lot of sense. I have used Application.Run before as well, mostly when I wanted to execute a proc resident in another workbook that was also open.

I think I have enough now (with some hints from the link suggested by Dave) to get into real trouble.

You mention of a KB, precipitated a thought on another problem I have encountered when trying to get one appl type to work with another. I have an MSProject appl that uses Excel for certain displays and plots (just a lot easier to do it that way). Writing data from MSProject to the xls workbook is quite simple. But when I try to start Excel from MSProject (simple hyperlink to the target xls file) so the user can interact with it, the procedures in my Personal.xls file are not available to that Excel instance. If I simply leave MSProject running and manually start Excel, everything works fine. Any thoughts on what might be happening here?

Thanks

UPDATE: I am having a problem getting the Excel appl to "go to sleep" while the user is interacting with Word. I have tried Application.Wait and the Sleep API, but both seem to completely take over the system for the delay time and locks everything up. I thought I could:

• set a global boolean (gb) to false
• hide the current form
• initiate the modeless form
• start the "timer" for, say, 5 seconds
• keep checking for the gb value = true each time the timer expired.
• If gb is still false, the code loops to start the timer again.
• If gb is true, the loop is exited, the original form is "unhidden" and the rest of the proc continues
• Exiting the modeless form sets the global boolen to true.

The logic seems to work, but execution effectively "stops" as soon as the timer starts.

So, I need a different type of delay, i.e., one that lets other things happen while the xls appl is "asleep". Any thoughts?

[TonyJollans]

OK, this is slightly more complicated than I suggested!

See the attached example.

[TonyJollans]

As to your other problem, Excel started using Automation, does not open AddIns.

If you want Personal to be available, you can open it explicitly - but be aware that if you have another instance of Excel running, it will have Personal.xls open for update and you will be prompted to open as Read-only.

[geekgirlau]

Quick suggestion to put Excel to sleep - use DoEvents within a timer loop