Linked excel objects in word, and my resulting problems :)

[Paul DeMarco]

When you want to specify a chart in excel you use something similar to:

[filename]sheet_name chart_name

why is the filename there for an internal workbook reference? worse yet, the oleformat.label property for a linked chart from word->excel uses:

sheet_name![filename]sheet_name chart_name

Whys the sheet there twice? These questions dont matter, just odd, the next part is the real reason for the post. The problem with all this is what when the linkformat.sourcefullname property on that linked chart changes in word, it cannot find the new chart because the oleformat.label property still contains the old filename, which was changed.

so how is someone supposed to go about changing the filename that a linked chart refers to? the oleformat.label property is readonly. I do not think I can simply remake the oleformat object, although that would be suitable.

I realize I could completely recreate the linked chart in word, but the list of properties and child objects to copy settings from would be immense to guarantee that everything was replicated as is.

Also, when changing the source of a linked excel object in a word document (that has been resized), it reverts to its full size. Is there anyway to preserve its position and size, without having to reset them after changing the linkformat.sourcefullname property.

Finally, since that makes 2 questions, heres the 3rd and final problem

When you want to programatically change the source data range for a series on an excel chart, it becomes difficult because it doesn't expose the existing source range as a range, simply as a string. but that would be less then convenient to parse, is there an easy way to get that into a range object, so that things like its sheet, and cells are easily accessible. once that information is there, then its easy to create a new range and set it as the new source data range for the chart series.

thanks for any and all comments/help.

 

[Jon Peltier]

Paul -

I can't really offer much about the whole OLE object thing, except that
wherever practical I avoid it by copying and pasting pictures of the
charts. In general I've found it no slower to update the chart in Excel
and copy over a new picture, than to update the OLE object, and the
chart picture routine is much easier.</soapbox>

About the source data range.... Excel doesn't expose the data range,
because most of the time it's too complex, with different length series,
discontiguous ranges, even different sheets. But you can access the
series formula for each series, and parse this to get the ranges. John
Walkenbach has even written a handy class module that helps you access
the actual ranges directly:

http://www.j-walk.com/ss/excel/tips/tip83.htm

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

When you want to specify a chart in excel you use something similar
to:

[filename]sheet_name chart_name

why is the filename there for an internal workbook reference? worse
yet, the oleformat.label property for a linked chart from word->excel
uses:

sheet_name![filename]sheet_name chart_name

Whys the sheet there twice? These questions dont matter, just odd,
the next part is the real reason for the post. The problem with all
this is what when the linkformat.sourcefullname property on that
linked chart changes in word, it cannot find the new chart because
the oleformat.label property still contains the old filename, which
was changed.

so how is someone supposed to go about changing the filename that a
linked chart refers to? the oleformat.label property is readonly. I
do not think I can simply remake the oleformat object, although that
would be suitable.

I realize I could completely recreate the linked chart in word, but
the list of properties and child objects to copy settings from would
be immense to guarantee that everything was replicated as is.

Also, when changing the source of a linked excel object in a word
document (that has been resized), it reverts to its full size. Is
there anyway to preserve its position and size, without having to
reset them after changing the linkformat.sourcefullname property.

Finally, since that makes 2 questions, heres the 3rd and final
problem

When you want to programatically change the source data range for a
series on an excel chart, it becomes difficult because it doesn't
expose the existing source range as a range, simply as a string. but
that would be less then convenient to parse, is there an easy way to
get that into a range object, so that things like its sheet, and
cells are easily accessible. once that information is there, then
its easy to create a new range and set it as the new source data
range for the chart series.

thanks for any and all comments/help.

 

[Paul DeMarco]

Thank you for the link, that is indeed helpful.

I sadly will have a very hard time moving away from the OLE links, as I know nothing about what they will/could be linked to.

Paul -

I can't really offer much about the whole OLE object thing, except that
wherever practical I avoid it by copying and pasting pictures of the
charts. In general I've found it no slower to update the chart in Excel
and copy over a new picture, than to update the OLE object, and the
chart picture routine is much easier.</soapbox>

About the source data range.... Excel doesn't expose the data range,
because most of the time it's too complex, with different length series,
discontiguous ranges, even different sheets. But you can access the
series formula for each series, and parse this to get the ranges. John
Walkenbach has even written a handy class module that helps you access
the actual ranges directly:

http://www.j-walk.com/ss/excel/tips/tip83.htm

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

When you want to specify a chart in excel you use something similar
to:

[filename]sheet_name chart_name

why is the filename there for an internal workbook reference? worse
yet, the oleformat.label property for a linked chart from word->excel
uses:

sheet_name![filename]sheet_name chart_name

Whys the sheet there twice? These questions dont matter, just odd,
the next part is the real reason for the post. The problem with all
this is what when the linkformat.sourcefullname property on that
linked chart changes in word, it cannot find the new chart because
the oleformat.label property still contains the old filename, which
was changed.

so how is someone supposed to go about changing the filename that a
linked chart refers to? the oleformat.label property is readonly. I
do not think I can simply remake the oleformat object, although that
would be suitable.

I realize I could completely recreate the linked chart in word, but
the list of properties and child objects to copy settings from would
be immense to guarantee that everything was replicated as is.

Also, when changing the source of a linked excel object in a word
document (that has been resized), it reverts to its full size. Is
there anyway to preserve its position and size, without having to
reset them after changing the linkformat.sourcefullname property.

Finally, since that makes 2 questions, heres the 3rd and final
problem

When you want to programatically change the source data range for a
series on an excel chart, it becomes difficult because it doesn't
expose the existing source range as a range, simply as a string. but
that would be less then convenient to parse, is there an easy way to
get that into a range object, so that things like its sheet, and
cells are easily accessible. once that information is there, then
its easy to create a new range and set it as the new source data
range for the chart series.

thanks for any and all comments/help.