embedded Excel objects in Word

[lee hite]

i am having a devil of a time manipulating embedded Excel objects in
Word documents via OLE automation. everything works exactly as it
should, it's just terribly unreliable. i can run the exact same code
against the exact same data and it will run to completion fine most of
the time, but not 100%. out of the clear blue i'll get seemingly
random errors -- for example i'll get an "Excel - file could not be
accessed" window, which will be followed by an "unexpected error has
occurred" message, followed by an "object was created in Excel --
application not available to open object". or another message that i
see a lot of is "Word has encountered a problem and needs to close".
i've tried using ON ERROR to trap these problems, and sometimes that
works and sometimes it doesn't. and even when it does, i still get
the program interrupted with the Word message window, which pretty
much defeats the purpose of the error trap.

the code i'm using is pretty straight forward -- i'm not sure there's
any other way to accomplish what i want:

Set objShape = objWord.ActiveDocument.InlineShapes(1)
objShape.OLEFormat.Open

once i have the Excel object opened, there's a lot of manipulation
that i do -- but i can remove all that code and the problems still
occur. the simple act of opening the embedded object inevitably
causes the program to crash. i've also experimented with early
binding vs. late binding, and that doesn't help.

this is with Office 2003 SP2 and i have the problems on all versions
of windows -- 2000, XP, and Vista.

is there anyway to use this OLEFormat object with any degree of
reliability, or is this just the nature of the beast?

lee

 

[Cindy M.]

Hi Lee,

is there anyway to use this OLEFormat object with any degree of
reliability, or is this just the nature of the beast?

To a certain extent, it's the nature of the beast. Personally, I rarely
encounter the kinds of things you describe, but if your system is a bit
unstable or there's some kind of interference (the user working with
Excel at the same time you're automating), then problems are bound to
occur...

FWIW, I've always had the most luck when working with embedded Excel
objects when I force them to open in a separate Excel window (as opposed
to editing in-place). There's no way to "quit" Excel when working in-
place via automation, so conflicts are much more likely.

To force the Excel sheet to open in a separate window, I always use
DoVerb. If Excel could already be running, I check that (Tasks.Exist)
before starting and store the state in a variable. If it wasn't running,
I send Quit to the Excel application. If it was, I make sure to close
the workbook. And in any case, it is of course important to set all
object variables to Nothing.

I am having a devil of a time manipulating embedded Excel objects in
Word documents via OLE automation. everything works exactly as it
should, it's just terribly unreliable. i can run the exact same code
against the exact same data and it will run to completion fine most of
the time, but not 100%. out of the clear blue i'll get seemingly
random errors -- for example i'll get an "Excel - file could not be
accessed" window, which will be followed by an "unexpected error has
occurred" message, followed by an "object was created in Excel --
application not available to open object". or another message that i
see a lot of is "Word has encountered a problem and needs to close".
i've tried using ON ERROR to trap these problems, and sometimes that
works and sometimes it doesn't. and even when it does, i still get
the program interrupted with the Word message window, which pretty
much defeats the purpose of the error trap.

the code i'm using is pretty straight forward -- i'm not sure there's
any other way to accomplish what i want:

Set objShape = objWord.ActiveDocument.InlineShapes(1)
objShape.OLEFormat.Open

once i have the Excel object opened, there's a lot of manipulation
that i do -- but i can remove all that code and the problems still
occur. the simple act of opening the embedded object inevitably
causes the program to crash. i've also experimented with early
binding vs. late binding, and that doesn't help.

this is with Office 2003 SP2 and i have the problems on all versions
of windows -- 2000, XP, and Vista.

is there anyway to use this OLEFormat object with any degree of
reliability, or is this just the nature of the beast?

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question
or reply in the newsgroup and not by e-mail