Strange problem with embedded Excel chart

[Gary McGill]

Hi,

[Note: all Office 2003, Windows XP]

This is NOT a question about linking to external Excel workbooks.

I have a PowerPoint presentation that contains an embedded (not linked)
Excel chart. When I look at this in PowerPoint without opening the embedded
Excel object, it's displaying the wrong data. When I open the embedded Excel
object, it displays the correct data, and if I close the embedded object
again (without making changes), the PowerPoint presentation now shows the
correct data. It's as if (as I suspect is the case) PowerPoint has a bitmap
"cache" of what the embedded object looks like - and that cache is out of
date.

More detail:

The data in the embedded Excel object has been modified programatically
(don't ask). When I open the PowerPoint presentation, the main part of the
chart is correct (i.e. it reflects the data inserted by the program).
However, there's a floating textbox on top of the chart which is linked to a
cell on the same worksheet that contains the chart data (still part of the
embedded Excel chart/workbook), and that's not being updated until I
manually open the embedded object. So, I've got a pie chart showing correct
data, with an over-laid textbox showing wrong data. (The data shown in the
textbox is as it was prior to the data being programatically modified -
until I open the embedded object, at which point its updated to reflect the
actual data).

Has anyone else experienced this problem? Is there a way to force PowerPoint
to update it's "cache", if that's the problem?

TIA,
Gary

 

[Gary McGill]

Steve,

You said:

That's likely the problem. There should be an update method the code can
call
on the chart or excel application while manipulating the data. I know
there's
one for MSGraph and Excel should be similar.

That sounds hopeful, and I want to believe this is the answer... but I'm a
little sceptical.

As I mentioned in my original post, the chart itself (as opposed to the
textbox positioned on top of the chart) is up-to-date. It's the textbox
that's the problem, so a method that updates the chart might not help? Then
again, I suppose the textbox is "part of" the chart in which case it might.
There is a Refresh() method on the chart object, but I can find no
information about what that actually does... It sounds worth a try (though
I'd be disappointed to have to do this since my code doesn't "know" anything
about the existence of the chart - it just updates the data)

What method are you referring to in MsGraph? There are all sorts of
"update"s you can do in Excel (Application.Calculate,
Application.ScreenUpdating, etc.) which all do different sorts of things,
and I'm not sure what kind of update you were referring to?

Hi,

[Note: all Office 2003, Windows XP]

This is NOT a question about linking to external Excel workbooks.

I have a PowerPoint presentation that contains an embedded (not linked)
Excel chart. When I look at this in PowerPoint without opening the
embedded
Excel object, it's displaying the wrong data. When I open the embedded
Excel
object, it displays the correct data, and if I close the embedded object
again (without making changes), the PowerPoint presentation now shows the
correct data. It's as if (as I suspect is the case) PowerPoint has a
bitmap
"cache" of what the embedded object looks like - and that cache is out of
date.

More detail:

The data in the embedded Excel object has been modified programatically

That's likely the problem. There should be an update method the code can
call
on the chart or excel application while manipulating the data. I know
there's
one for MSGraph and Excel should be similar.

(don't ask). When I open the PowerPoint presentation, the main part of
the
chart is correct (i.e. it reflects the data inserted by the program).
However, there's a floating textbox on top of the chart which is linked
to a
cell on the same worksheet that contains the chart data (still part of
the
embedded Excel chart/workbook), and that's not being updated until I
manually open the embedded object. So, I've got a pie chart showing
correct
data, with an over-laid textbox showing wrong data. (The data shown in
the
textbox is as it was prior to the data being programatically modified -
until I open the embedded object, at which point its updated to reflect
the
actual data).

Has anyone else experienced this problem? Is there a way to force
PowerPoint
to update it's "cache", if that's the problem?

TIA,
Gary

-----------------------------------------
Steve Rindsberg, PPT MVP
PPT FAQ: www.pptfaq.com
PPTools: www.pptools.com
================================================

 

[Gary McGill]

Steve,

Thanks for your continued help here...

I think we're slightly at cross-purposes, because the terminology I used in
my original post was ambiguous/misleading.

I have a single embedded object (an Excel chart) on my PowerPoint slide. The
textbox I refer to is not a separate object on the PowerPoint slide -
rather, it's an autoshape on the Excel chart "canvas". So, the textbox is
"part of" the chart in that it's displayed within (and cannot be moved out
of) the chart area - but in another sense it's not "part of" the chart,
because, well, it's floating on top of "the chart".

The "formula" for the textbox/autoshape references a cell on the same
worksheet that the chart data comes from (which of course is in the same
workbook as the chart). I set this up by selecting the autoshape and typing
"=Sheet1!$A$1" (or whatever) in the formula bar. As I manually update the
data, the content of the textbox changes - which is the effect I'm after
when I update the data through code.

However, when my code updates the data, the 'preview' in PowerPoint shows
the correct "chart" (the pie chart bit), but the previous (unchanged)
content of the textbox/autoshape. When I open the embedded Excel object,
Excel displays the updated data - which is then reflected in PowerPoint when
I come back out.

I did try making my code call the Refresh method on the chart object after
it's updated the data, but alas that didn't help. Note that when I said
"It's the textbox that's the problem, so a method that updates the chart
might not help" I was speculating that chart.Refresh might not update
non-core elements such as autoshapes positioned on top of the chart like my
textbox.

I've posted an example 1-slide presentation containing a chart that shows
the problem here: http://senduit.com/d8a2d2 (this will expire in 1 week).
When you open it, you'll see a message at the top-left of the chart which
shouldn't be there - and which disappears when you open the embedded Excel
object.

Thanks again,
Gary

Steve,

You said:


That sounds hopeful, and I want to believe this is the answer... but I'm
a
little sceptical.

As I mentioned in my original post, the chart itself (as opposed to the
textbox positioned on top of the chart) is up-to-date.

I'm confused, then, because you originally posted:

"I have a PowerPoint presentation that contains an embedded (not linked)
Excel chart. When I look at this in PowerPoint without opening the
embedded
Excel object, it's displaying the wrong data. "

But if you say the chart's ok, then it's ok.

It's the textbox
that's the problem, so a method that updates the chart might not help?

That's correct. And I did miss something that now seems more obvious:

If you have two different bits embedded from the same worksheet (your
chart and
your text), you have an embedded copy of the worksheet for *each* object.
They're independent. Changing one shouldn't change the other.

If these are two different objects, (no matter that they were originally
copied
from the same XLS), they'll both have to be updated independently.

Then

again, I suppose the textbox is "part of" the chart in which case it
might.
There is a Refresh() method on the chart object, but I can find no
information about what that actually does... It sounds worth a try
(though
I'd be disappointed to have to do this since my code doesn't "know"
anything
about the existence of the chart - it just updates the data)

But you don't SEE the data in PPT or any other OLE client. You see a WMF
picture of the representation of the data. It's that which needs
updating.

What method are you referring to in MsGraph?
Update

There are all sorts of
"update"s you can do in Excel (Application.Calculate,
Application.ScreenUpdating, etc.) which all do different sorts of things,
and I'm not sure what kind of update you were referring to?

Hi,

[Note: all Office 2003, Windows XP]

This is NOT a question about linking to external Excel workbooks.

I have a PowerPoint presentation that contains an embedded (not
linked)
Excel chart. When I look at this in PowerPoint without opening the
embedded
Excel object, it's displaying the wrong data. When I open the embedded
Excel
object, it displays the correct data, and if I close the embedded
object
again (without making changes), the PowerPoint presentation now shows
the
correct data. It's as if (as I suspect is the case) PowerPoint has a
bitmap
"cache" of what the embedded object looks like - and that cache is out
of
date.

More detail:

The data in the embedded Excel object has been modified
programatically

That's likely the problem. There should be an update method the code
can
call
on the chart or excel application while manipulating the data. I know
there's
one for MSGraph and Excel should be similar.


(don't ask). When I open the PowerPoint presentation, the main part of
the
chart is correct (i.e. it reflects the data inserted by the program).
However, there's a floating textbox on top of the chart which is
linked
to a
cell on the same worksheet that contains the chart data (still part of
the
embedded Excel chart/workbook), and that's not being updated until I
manually open the embedded object. So, I've got a pie chart showing
correct
data, with an over-laid textbox showing wrong data. (The data shown in
the
textbox is as it was prior to the data being programatically
modified -
until I open the embedded object, at which point its updated to
reflect
the
actual data).

Has anyone else experienced this problem? Is there a way to force
PowerPoint
to update it's "cache", if that's the problem?

TIA,
Gary


-----------------------------------------
Steve Rindsberg, PPT MVP
PPT FAQ: www.pptfaq.com
PPTools: www.pptools.com
================================================

-----------------------------------------
Steve Rindsberg, PPT MVP
PPT FAQ: www.pptfaq.com
PPTools: www.pptools.com
================================================

 

[Gary McGill]

Steve,

But the example (which I've d/l'd and poked at a bit) seems
to illustrate something different, in that I can't force it to update
manually
.. the text box goes away altogether.

When I open the embedded Excel object, the text box "disappears" in that it
becomes invisible (because the cell that it links to is empty) - but it's
still there, and I can select it by clicking around the top-left of the pie
chart (near the corner of the chart area). When I select it, the formula bar
still says "=Data!$L$12", which is correct. If I enter text in that cell and
go back to the chart, I see the text appear in the textbox. Are you quite
sure you're getting a different effect?

Gary

Steve,

Thanks for your continued help here...

No problem. It's an interesting problem.

I think we're slightly at cross-purposes, because the terminology I used
in
my original post was ambiguous/misleading.

The ambiguity arises because I'm a PPTGeek but ExcelWeak. You say "text
box"
and I (mistakenly) assume "PPT text box".

But we're closing in on it ... ;-)

I have a single embedded object (an Excel chart) on my PowerPoint slide.
The
textbox I refer to is not a separate object on the PowerPoint slide -
rather, it's an autoshape on the Excel chart "canvas". So, the textbox is
"part of" the chart in that it's displayed within (and cannot be moved
out
of) the chart area - but in another sense it's not "part of" the chart,
because, well, it's floating on top of "the chart".

The "formula" for the textbox/autoshape references a cell on the same
worksheet that the chart data comes from (which of course is in the same
workbook as the chart). I set this up by selecting the autoshape and
typing
"=Sheet1!$A$1" (or whatever) in the formula bar. As I manually update the
data, the content of the textbox changes - which is the effect I'm after
when I update the data through code.

OK, I follow that. But the example (which I've d/l'd and poked at a bit)
seems
to illustrate something different, in that I can't force it to update
manually
.. the text box goes away altogether.

However, when my code updates the data, the 'preview' in PowerPoint shows
the correct "chart" (the pie chart bit), but the previous (unchanged)
content of the textbox/autoshape. When I open the embedded Excel object,
Excel displays the updated data - which is then reflected in PowerPoint
when
I come back out.

I did try making my code call the Refresh method on the chart object
after
it's updated the data, but alas that didn't help. Note that when I said
"It's the textbox that's the problem, so a method that updates the chart
might not help" I was speculating that chart.Refresh might not update
non-core elements such as autoshapes positioned on top of the chart like
my
textbox.

I've posted an example 1-slide presentation containing a chart that shows
the problem here: http://senduit.com/d8a2d2 (this will expire in 1 week).
When you open it, you'll see a message at the top-left of the chart which
shouldn't be there - and which disappears when you open the embedded
Excel
object.

Thanks again,
Gary

Steve,

You said:

That's likely the problem. There should be an update method the
code
can
call
on the chart or excel application while manipulating the data. I
know
there's
one for MSGraph and Excel should be similar.

That sounds hopeful, and I want to believe this is the answer... but
I'm
a
little sceptical.

As I mentioned in my original post, the chart itself (as opposed to
the
textbox positioned on top of the chart) is up-to-date.

I'm confused, then, because you originally posted:

"I have a PowerPoint presentation that contains an embedded (not
linked)
Excel chart. When I look at this in PowerPoint without opening the
embedded
Excel object, it's displaying the wrong data. "

But if you say the chart's ok, then it's ok.

It's the textbox
that's the problem, so a method that updates the chart might not help?

That's correct. And I did miss something that now seems more obvious:

If you have two different bits embedded from the same worksheet (your
chart and
your text), you have an embedded copy of the worksheet for *each*
object.
They're independent. Changing one shouldn't change the other.

If these are two different objects, (no matter that they were
originally
copied
from the same XLS), they'll both have to be updated independently.

Then
again, I suppose the textbox is "part of" the chart in which case it
might.
There is a Refresh() method on the chart object, but I can find no
information about what that actually does... It sounds worth a try
(though
I'd be disappointed to have to do this since my code doesn't "know"
anything
about the existence of the chart - it just updates the data)

But you don't SEE the data in PPT or any other OLE client. You see a
WMF
picture of the representation of the data. It's that which needs
updating.

What method are you referring to in MsGraph?

Update

There are all sorts of
"update"s you can do in Excel (Application.Calculate,
Application.ScreenUpdating, etc.) which all do different sorts of
things,
and I'm not sure what kind of update you were referring to?

Hi,

[Note: all Office 2003, Windows XP]

This is NOT a question about linking to external Excel workbooks.

I have a PowerPoint presentation that contains an embedded (not
linked)
Excel chart. When I look at this in PowerPoint without opening the
embedded
Excel object, it's displaying the wrong data. When I open the
embedded
Excel
object, it displays the correct data, and if I close the embedded
object
again (without making changes), the PowerPoint presentation now
shows
the
correct data. It's as if (as I suspect is the case) PowerPoint has
a
bitmap
"cache" of what the embedded object looks like - and that cache is
out
of
date.

More detail:

The data in the embedded Excel object has been modified
programatically

That's likely the problem. There should be an update method the
code
can
call
on the chart or excel application while manipulating the data. I
know
there's
one for MSGraph and Excel should be similar.


(don't ask). When I open the PowerPoint presentation, the main part
of
the
chart is correct (i.e. it reflects the data inserted by the
program).
However, there's a floating textbox on top of the chart which is
linked
to a
cell on the same worksheet that contains the chart data (still part
of
the
embedded Excel chart/workbook), and that's not being updated until
I
manually open the embedded object. So, I've got a pie chart showing
correct
data, with an over-laid textbox showing wrong data. (The data shown
in
the
textbox is as it was prior to the data being programatically
modified -
until I open the embedded object, at which point its updated to
reflect
the
actual data).

Has anyone else experienced this problem? Is there a way to force
PowerPoint
to update it's "cache", if that's the problem?

TIA,
Gary


-----------------------------------------
Steve Rindsberg, PPT MVP
PPT FAQ: www.pptfaq.com
PPTools: www.pptools.com
================================================




-----------------------------------------
Steve Rindsberg, PPT MVP
PPT FAQ: www.pptfaq.com
PPTools: www.pptools.com
================================================

-----------------------------------------
Steve Rindsberg, PPT MVP
PPT FAQ: www.pptfaq.com
PPTools: www.pptools.com
================================================

 

[Gary McGill]

Some more info - and a discovery (albeit not quite of the Eureka! variety).

I implied (and possibly even said directly) that my code doing the updating
was VBA; that's not strictly true - it's using the VBA object model, but the
code is actually written in C# / .NET. Part of my difficulty in diagnosing
this problem has been that it's a huge program (tens of thousands of lines),
and reducing it to a point where I could easily debug it would have been
difficult.

So, I just tried reproducing the same steps (or my best guess at the same
steps) in VBA, and so far I haven't been able to get the problem to occur by
that method. Which really doesn't help

However, I discovered something else. One of the things my code is doing is
deleting a row at the top of the data worksheet. If I stop it from doing
that, then the problem with the non-updating textbox goes away... (sounds
strange, but it does seem very consistent after dozens of tests).

I also discovered that if I move the row that's getting deleted down so that
it's underneath the cell where the textbox gets its data from (and so that
the address of that cell isn't affected when the row is deleted), then the
problem also goes away. In my particular case, that's an acceptable
work-around.

Apologies to those in the group who emailed me hoping that I'd found an
answer - the fact that I now have a work-around means I'm less inclined to
spend much more time trying to find a "better" answer. However, if anyone
has any new theories in light of this new info, I'd be happy to try them
out.

Gary

 

[Gary McGill]

Steve,

I think your post crossed with mine. As I said in that other post, I haven't
actually been able to reproduce the problem in pure VBA, even though I'm
pretty sure I'm doing the same thing as my real program.

I was still interested in your code, though, because I noticed you didn't
activate the embedded object (oShape.OleFormat.Activate) prior to accessing
the embedded workbook (oShape.OleFormat.Object). I have all kinds of
problems with embedded object activation (not least because there's no
deactivate method), so I was quite excited when I saw that. However, having
tried it, it crashed Excel - which is maybe what you meant when you said
"Excel's now in a snit here and wont' behave".

Thanks very much for your help on this - had you not showed continued
interest, I would have given up and not found the workaround that I did.

Gary

 

[Gary McGill]

Steve,

You're right that the textbox uses an absolute cell ref (because you can't
use relative refs from there - Excel won't let you), but in the posted
example, Excel has updated the reference correctly (*). You're seeing the
post-changes version where it points at L12; in the pre-changes version it
was pointing at the same cell - which at that time was L13.

The cell should indeed be empty since in that particular example I wanted
the message to disappear (which indeed it does as soon as you open the Excel
object).

I did try using a named range to see if that helped, but what actually
happened was that the textbox became entirely disconnected (i.e. it ended up
with no formula). Not what I expected at all!

(*) Hmmm.... I'm assuming something here about *when* the reference is
updated. When I open the Excel object and look at it, it says L12 BUT by the
time I've opened it the contents of the textbox have been fixed too. So,
it's possible that - like the textbox contents - the reference isn't getting
updated until I open the Excel object. Is the 'fridge light on when the door
is shut...?

Gary

 

[Gary McGill]

Steve,

I noticed that, but when I used a named range, it seemed to work here ...
at
least the formula didn't disappear on me. I wonder what provoked that?

Here's what happens when I do this manually:

1. Open embedded object
2. Create named range ("foo")
3. Point textbox at named range ("=foo")

At this point it all looks OK - if I click on the textbox it says "=foo" in
the formula bar.

However, if I close the embedded object and open it again, when I click on
the textbox it's not linked to anything. The formula bar is blank.

I thought for a minute it was to do with the Excel object being embedded,
but I tried saving it out to a real Excel file and doing the same thing:
same result.

Looks like a bug, smells like a bug...

Gary

 

[Gary McGill]

Steve,

It sounds like you've been battling with different problems from me!
Unfortunately, having taken that detour I'm not sure that you've come all
the way back to where I am.

* I too can manually edit the data and have the chart (including the
textbox) update. It's only when I modify the data via my code that I come up
against the original problem (and remember that I can't even reproduce this
in VBA - it's my c# / .NET code that's affected)

* You say that you can use a named range without problems, but so can I -
until I delete a row that changes the address of the range AND I then come
out of the embedded object and go back in. That's when it breaks.

Anyway, I think I'm going to leave it there. The mysteries of Excel and OLE
embedding are sometimes too deep and too dark to ponder for too long.

Thanks for all your help with this - I really appreciate it. As I said a
couple of posts ago, I wouldn't have arrived at a workaround had you not
chivvied me on

Gary