accessing excel in word

[starl]

Is it possible to have a macro open a form in Word, accept some input, then launch Excel, draw a graph, then plop that graph into the form (all without the reader having to be aware that Excel was even called)? Failing such a high-wire act, can I do this sort of thing just for doing some calculations, and deliver the answer back in the form? Here is a dirt-simple example: I want the reader to enter a number, and I will return the square root of that number (not a serious application, but if I can get that one to work, I can build from there.)

thanks

 

[Damon Ostrander]

Hi starl,

Yes, it is possible to do everything you describe using Automation. If you are not already familiar with Automation, see the topic "understanding automation" in either the Word or Excel VBA helps. Doing this will require some understanding of both the Word and Excel object models. These are adequately documented in the VBA helps, but you will still require a lot of trials (in both senses of the word).

May I suggest that instead of having the macro create an Excel graph from scratch and paste it into the Word form, that instead you create the graph ahead of time--using preliminary data--and paste this into the Word form as an unlinked Excel object? Then hide this graph (I believe you have to do this in VBA). Then all your macro has to do is enter the data into the worksheet from VBA (very easy), unhide the graph, and viola!

 

[starl]

The graph needs to be created with information input by the reader - so it can't be designed ahead of time.

my excel vba skills are passable, my word vba... in it's infancy - like treading water and I can barely keep my head up!

 

[Damon Ostrander]

Hi again starl,

Yes, I understood that the data the graph would plot depends on the users' inputs. Nevertheless, you can set up the graph ahead of time and then modify the data the graph plots, as well as titles, axis labels, etc., after the user enters the data. This saves you from having to do the copy and paste from Excel in your macro, which is a bit tricky (the tricky part is in getting it to paste the chart into Word in the right place, sizing it correctly and setting all its properties, e.g., how text wraps around it, correctly.

Yes, this is difficult if you have little or no prior experience with VBA programming in Word. This problem is just big enough to be unlikely to elicit a complete solution when posted on this board. I suggest you do a search on this site for similiar examples, and also suggest you contact Dreamboat. She is extremely helpful with Word-related problems and also has an excellent website which may provide some useful examples and even some direct help.

 

[starl]

ok - I see what you mean now.. instead of creating the graph from scratch, just modify an existing one! gotcha - yes,that would be much simpler!!!

 

[Anne Troy]

TOO funny, Damon!! starl and I are *off-site* friends. She couldn't reach me last night, LOL!!

She just pointed me here to see what you said. Thanks SOOOO much for your generous compliments!!!

~Anne

 

[Damon Ostrander]

Dreamboat,

Wonders never cease!

I am amazed at how much you get around! Do you sleep?

Damon