Strange problem with embedded Excel chart

  • Thread starter Thread starter Gary McGill
  • Start date Start date
G

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
 
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
Click to expand...

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.
 
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.
Click to expand...

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?

Steve Rindsberg said:
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
Click to expand...

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
Click to expand...

-----------------------------------------
Steve Rindsberg, PPT MVP
PPT FAQ: www.pptfaq.com
PPTools: www.pptools.com
================================================
Click to expand...
 
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.
Click to expand...

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?
Click to expand...

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)
Click to expand...

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?

Steve Rindsberg said:
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
Click to expand...

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
Click to expand...

-----------------------------------------
Steve Rindsberg, PPT MVP
PPT FAQ: www.pptfaq.com
PPTools: www.pptools.com
================================================
Click to expand...
Click to expand...
 
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 Rindsberg said:
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.
Click to expand...

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?
Click to expand...

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)
Click to expand...

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?

Steve Rindsberg said:
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
================================================
Click to expand...
Click to expand...

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

Thanks for your continued help here...
Click to expand...

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.
Click to expand...

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.
Click to expand...

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 Rindsberg said:
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.
Click to expand...

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?
Click to expand...

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)
Click to expand...

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
================================================
Click to expand...

-----------------------------------------
Steve Rindsberg, PPT MVP
PPT FAQ: www.pptfaq.com
PPTools: www.pptools.com
================================================
Click to expand...
Click to expand...
 
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.
Click to expand...

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 Rindsberg said:
Steve,

Thanks for your continued help here...
Click to expand...

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.
Click to expand...

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.
Click to expand...

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 Rindsberg said:
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
================================================
Click to expand...
Click to expand...

-----------------------------------------
Steve Rindsberg, PPT MVP
PPT FAQ: www.pptfaq.com
PPTools: www.pptools.com
================================================
Click to expand...
 
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
 
OK, now I think you've got me up to speed. Thanks for hanging in there.

Try something simple like this:

Sub GetXLSWorksheetDataExample()
' This assumes you've set a reference to the MS Excel object library in the IDE

Dim oWorkbook As Excel.Workbook
Dim oWorksheet As Excel.Worksheet
Dim oSh As Shape

' This example assumes you've selected the excel object
' you want to work with
Set oSh = ActiveWindow.Selection.ShapeRange(1)

Set oWorkbook = oSh.OLEFormat.Object
' Use the first sheet in the work book
Set oWorksheet = oWorkbook.worksheets(1)

With oWorksheet
.Activate
.Range("L12").Value = "DoobaDoobaDAY!"
End With

oWorkbook.Close (False)
Set oWorkbook = Nothing
Set oWorksheet = Nothing

End Sub

It seems to be working here, though for some other reason Excel's now in a snit
here and wont' behave.

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.
Click to expand...

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 Rindsberg said:
Steve,

Thanks for your continued help here...
Click to expand...

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.
Click to expand...

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.
Click to expand...

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
================================================
Click to expand...

-----------------------------------------
Steve Rindsberg, PPT MVP
PPT FAQ: www.pptfaq.com
PPTools: www.pptools.com
================================================
Click to expand...
Click to expand...
 
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
 
So maybe you just lit the burner? <g> The formula in your rectangle uses
absolute cel refs, as I recall. Meaning that if you remove row 1, everything
moves up a row but the forumla still points to row 12, which was the old row
13, now a blank cell.

Try changing the formula to L12 or better yet, create a range name (eg
LabelText) for the cell with the data and then use Data!LabelText as your
formula. See if that sorts it.
 
Apologies for that ... I was chopping out some other irrelevant bits of code
from the example and swatted more than I intended to.

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

Steve Rindsberg said:
OK, now I think you've got me up to speed. Thanks for hanging in there.

Try something simple like this:

[snip]
Click to expand...
Click to expand...
 
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
 
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)
Click to expand...

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?

Yeah, it's kinda hard to see inside the fridge, especially when you can't open
the door 'cause both hands are busy doing one-handed clapping exercises. If a
programmer's head slams into a tree in the forest and there's nobody there to
hear it, does it still ache?

But I'd guess that updates happen when you change the data in an open object
and save or update before quitting.

, but in the posted
 
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?
Click to expand...

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
 
Steve,


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.
Click to expand...

There's other stuff going on here too, though.

Here, whenever I try to activate the embedded Excel, I first get a macro
warning. No matter whether I enable or disable, next I get a series of errors
about "Unable to connect to the network object" and finally another one wherein
MS mumbles vaguely about some feature not working and an offer to fix it. Yea
or nay, makes no diff. It stays broken.

I've previously saved the excel content out to an independent XLS file, so I
make a copy of that, and remove the VBA module, thinking there's an OLE timing
issue because of the delay while PPT's waiting for me to answer the macro
warning dialog.

I go to save the XLS and it tells me that it's signed and since I don't have
the correct yadayada ... signature will be lost. Fine. Save it and leave me
alone.

I then select the chart and copy it to a new PPT file.

Oh, somewhere in all this I've changed the forumla in the label box to
Data!RangeName (where rangename is the name I've assigned cell L12).

This all now works. I can activate the chart from PPT, switch to the data
sheet, change the value in L12, switch back to the chart, ... everything
updates.

What does it all mean? Not sure. The changes are to:

- Signature
- Macros vs No Macros
- Formula and the way I entered the range name
- Possibly different security settings on my machine vs yours?

Are we having fun yet?
 
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
 
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.
Click to expand...

Entirely possible. "Oh, look ... SHINY!" and off I go into the weeds ...

What I had in mind was to back up to something that worked repeatably manually
before we went back into the code.

That said, it sounds like you've got it working by alternate means.
Seems like a good time to move on, indeed.

'Sbeen fun, that's certain.
 
Back
Top