Excel OLE data in PPT reverting to OLD data after macro updation

[Hari Prasadh]

Hi,

Im cross-posting/multi-posting this in the hope that I could get a solution.

I am facing a very strange problem (both in Office 2002 and 2003) with Excel
OLE charts in PPT. (Im programming with excel as base)

I am using code zilched from Jon P's site in order to copy data from an
excel sheet and activate a PPT application (and Slide) and an Excel OLE
object within the slide and then updating the data in it and then saving
this template with a NEW NAME.

Im using this method to update a single slide template tons of time and each
time saving it with different (but well-defined) names.

Situation: - The new data gets pasted in the Excel worksheet behind the
chart (within PPT) and then the chart also gets updated and then I do a Save
As (using code) to save it with a new name and in a new location and then
close the file.

I then exit out of the code and then open the file manually and in the slide
show mode can see the UPDATED data but if I try to get in to editing mode of
the Excel OLE chart within PPT then the chart SUDDENLY displays the old data
and the excel worksheet also displays the old data??

If I escape out of the OLE and do Ctrl + Z then it shows the data updated
through Macro.

Couple of my colleagues were running tests and they see that if they save
the file manually after updating data through Macro then the same problem
doesnt recur? Im not very sure why it is so and nor Do I know whether the
above observation about manual saving is correct for all situations.

For me manual saving is not an option.

We are also experiencing the same problem in updating excel worksheets
within PPT's.

Can anybody please guide me as to what is happening here?

Regards,
Hari
India

 

[Steve Rindsberg]

I don't follow what's going on here, but let me ask this: do you call the OLE
object's Update method before closing the app/releasing the reference to the
object's variable? What you're describing sounds like what happens when you
don't do this.

 

[Brian Reilly, MS MVP]

Hari,
First mistake is using any of Jon P's instructions. (VBG) Jon is a
good friend and a well respected MVP. But that doesn't stop us from
teasing him (g).

As Steve said we aren't sure what you are doing here (exactly by code)
so can you post the relevant parts.

Then we can let Jon Peltier answer his own mess (vbg). Couldn't resist
Jon, Hari is a recent regular in the PPT NG.

Hari, Jon is a regular stop in in the PPT NewsGroup and works
regularly in Excel and PPT integration all the time.

Brian Reilly, MVP

 

[Hari Prasadh]

Hi Steve/Brian,

Oops, sorry if I misled anyone. I got the idea (of controlling PPT from
excel) and great parts of the syntax from Jon and then "meshed" it with
snippets of other NG codes to customise it to my requirements. So bug is in
my meshing (and incorrect understanding of the same) and not with Jon's
code.

do you call the OLE object's Update method before closing the app/releasing
the reference to the object's variable?

Im not sure what you mean by Update method. Please tell me about the same.

I have 2 sets of codes: -

a) First is I receive CSV file having some data. I transpose, sort perform
Statistical tests on it and get it in to a form where it could be readily
pasted in to PPT. This is one macro.

b) Second is taking the formatted range in the CSV file above and calling a
SUB which would paste this Data in to an already existing Excel chart OLE
object within a PPT (having a single slide). (The PPT file may or may not be
open depending on whether am updating data of other objects in the slide or
not).

It is the part b) with which Im having the present problem and am posting
part b) below.

(Latest update : - My colleagues included a line of code below -
ograph.save - (ograph is the name of the excel workbook which is an OLE in
PPT) which they claimed to solve the problem I posted, but I would still
like to double check from you. Steve, when you said "update" do you mean
save the object?)

Option Explicit
Public oPPTApp1 As PowerPoint.Application
Dim oPPTShape1 As PowerPoint.Shape
Dim rngNewRange1 As Excel.Range
Dim oGraph1 As Workbook
Dim Excelwksheet As Worksheet
Dim Excelchartsheet As chart
Dim path As String
Dim PPT As PowerPoint.Presentation
Dim a as Double
Dim b as Double
Dim c as Double
Dim d as Double

Sub UpdateExcelDataChart(filepath As String, objName As String, flag As
Integer, sheetname As String, chartname As String)

Set oPPTApp1 = CreateObject("PowerPoint.Application")

oPPTApp1.Visible = msoTrue

If flag = 0 Then

oPPTApp1.Presentations.Open (filepath)
Else

End If

'There is a single slide in the presentation so can afford to use slides(1)

With oPPTApp1.ActivePresentation.Slides(1)
For Each oPPTShape1 In .Shapes


'objname is the name of the object within PPT whose data needs to be
updated.

If oPPTShape1.Name = objName Then

'a, b, c and d are being used because Excel OLE objects in PPT have a habit
on going for getting dislocated while we are updating them.

a = oPPTShape1.Top

b = oPPTShape1.Left

c = oPPTShape1.Height

D = oPPTShape1.Width


If oPPTShape1.OLEFormat.progID = "Excel.Chart.8" Then

Set oGraph1 = oPPTShape1.OLEFormat.Object

Set Excelwksheet = oGraph1.Worksheets(sheetname)

' Before this sub was called we already have some data in the clipboard
hence pasting below.

Excelwksheet.Range("B1").PasteSpecial xlPasteValues


'Though am just updating the data in a workbook for the chart to get updated
am forced to activate
' the chart below as the view remains in worksheet based on above pasting.

Set Excelchartsheet = oGraph1.Charts(chartname)

Excelchartsheet.Activate

oPPTShape1.LockAspectRatio = msoFalse
oPPTShape1.Top = a
oPPTShape1.Left = b
oPPTShape1.Height = c
oPPTShape1.Width = D

End If
End If


Next oPPTShape1


End With

Application.DisplayAlerts = False

oPPTApp1.ActivePresentation.SaveAs ("D:\Dummy\NRAUTE001.ppt")

oPPTApp1.ActivePresentation.Close

End Sub



Thanks a lot,
Hari
India

 

[Jon Peltier]

He he. Read back aways, and you'll see that Hari abandoned my simpler
suggestion to update the charts in Excel and then put them into Ppt. He
prefers doing it the hard way....

Hari's trying to update Excel charts in Excel workbook OLE objects which
are embedded in PowerPoint. I've been strangely silent on this topic.

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

 

[Brian Reilly, MS MVP]

Jon,
You aren't usually silent. Strange, always! Just like me. (vbg)
Brian Reilly, MVP

 

[Hari Prasadh]

Hi Jon,

He prefers doing it the hard way....

As I said in the one of the earlier posts, the charts/graphs in each of the
slides have too much of custom formatting along with charts being of
different types, Stacked/simulating average line/Pie chart, Color of chart,
Placement of charts in the slides, font of the labels and hundreds of other
stuff. Also, this same stuff needs to be done for tons of slides, so making
charts in excel is too much of a pain (will require more coding).

I got the idea of updating charts template charts with new data (and saving
it with new name) from MS site where they have provided code for Updating an
MS graph applet. But it seems now have run in to other limitations.

Thanks a lot,
Hari
India

 

[Steve Rindsberg]

Oops, sorry if I misled anyone.

No, you just gave Brian a welcome opportunity to tease Jon.
Not that anyone needs to MAKE opportunities for Brian to do that. ;-)
Nor for me to tease Brian. We're all immune to one another, no worries.

Im not sure what you mean by Update method. Please tell me about the same.

I think that comes of my misunderstanding what you're up to. When automating
MSGraph or Excel objects from within PPT, you need to call Update before
closing the app or else the data isn't updated in the OLE object. The next
time you activate it, it'll hold the old data and the chart will revert to
whatever it previously was before you modified it.

It sounds as though your colleagues have set us straight on this.
Especially if the original object in PPT is linked to the excel data.
Saving would be necessary, yes.

 

[Tom Ogilvy]

MSGraph is not MSExcel. The data area behind an MSGraph chart is not an
excel worksheet. Perhaps that is your problem - trying to treat them the
same.

 

[Hari Prasadh]

Hi Tom,

Thanks for jumping in.

Actually I wanted to say that the idea of updating a range in a "template" I
got from a MS KB link article for updating MS graph applets.

I saw the syntax behind the same, especially the inter application
programming syntax and way the specified object is activated.

After that I got in to "cutomisation" mode for using it to update data
behind excel graph objects. So in effect am not treating Msgraph same as
MsCharts (Some time back Jon gave me a neat session on the differences
between the 2)

Thanks a lot,
Hari
India

 

[Jon Peltier]

Hari -

Is all you're doing changing the data that the embedded Excel workbook's
chart is based upon? Maybe I'm being dense, but I don't think you need
to activate the OLE object, or use copy-paste.

I created an Excel workbook that had a worksheet ("Sheet1") with data in
A1:A5, and a chart sheet ("Chart 1") based on this data. I shrank the
chart sheet, then copied it and pasted it as an Excel Chart Object into
a PowerPoint slide. Then I closed the Excel workbook in Excel without
saving, so it's gone.

I fiddled around with the PPT and XL Immediate windows, then came up
with this short little procedure, which I ran from another Excel workbook:

Sub GetXLinPPT()
Dim ppApp As PowerPoint.Application
Dim xlWbk As Excel.Workbook ' OLE object in ppApp

Set ppApp = GetObject(, "PowerPoint.Application")
Set xlWbk = _
ppApp.ActivePresentation.Slides(1).Shapes(1).OLEFormat.Object

xlWbk.Worksheets("Sheet1").Range("A1:A5").Value = _
WorksheetFunction.Transpose(Array(5, 4, 3, 2, 1))

Set xlWbk = Nothing
Set ppApp = Nothing
End Sub

The data in the OLE worksheet must have updated, because the chart
updated, and stayed updated. Then all I did was save the PowerPoint
file. When I reopened it, the changes were still there.

By not opening the OLE object, you might prevent the resizing and
repositioning it is susceptible to. This technique worked well enough
that I might even have to start using it. Hear that, Brian?

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

 

[Hari Prasadh]

Hi Jon,

Its fantabulous.

I tried it on one slide and it works flawlessly and no objects get
dislocated and neither repositioned . Moreover my code also becomes less
cluttered as now I wont have to remember object positions and what not . In
the old way of copy-paste even after remembering and forcibly applying the
positions the stuff was far from perfect and considerable manual formatting
was required.

Just to tell you in the old method I was also having problems of view of the
charts changing from charts to the worksheet behind the data, which caused
further aggravation for me.

Im surprised that this simple solution wasnt thought by before!!! (Brian,
cant resists from pulling your legs - I can hide under the cloak of being a
beginner but what about you!!)

This technique worked well enough that I might even have to start using it.
Hear that, Brian?

2 points I would like to add: -

a) In one of the earlier posts in PPT group I asked for project mgmt of PPT
automation using Excel and Brian asked a very important question as to the
base application (excel or PPT) im using for programming. I said excel
because I could pinch Jon's pre-built tutorials. I think one more important
needs to be added to the list which is as to whether one is creating objects
on the fly in PPT or whether one has a template in which one is populating
new data using Jon's latest method. The second would be extremely helpful
when the slides involve lot of custom/individual formatting differences
across objects (within slides/across slides). I think that way the if one
has a pre-built template the one doesnt have to "code" those custom
formatting within each individual sub. Less code, less debugging headache.

b) Jon if possible please add this new way of your to the web-site. Others
wont have to break their heads with PPT object dislocations.

Regards,
Hari
India

 

[Jon Peltier]

Hi Jon,

Its fantabulous.


I tried it on one slide and it works flawlessly and no objects get
dislocated and neither repositioned . Moreover my code also becomes less
cluttered as now I wont have to remember object positions and what not . In
the old way of copy-paste even after remembering and forcibly applying the
positions the stuff was far from perfect and considerable manual formatting
was required.

I was as surprised as you that it worked so nicely. Of course, my
example was a simple column chart, but even the simple charts have
problems with the OLE activate & paste techniques.

Just to tell you in the old method I was also having problems of view of the
charts changing from charts to the worksheet behind the data, which caused
further aggravation for me.

It is problems like this which have kept me away from using such objects
in my projects, and which keep Brian on my case.

Im surprised that this simple solution wasnt thought by before!!! (Brian,
cant resists from pulling your legs - I can hide under the cloak of being a
beginner but what about you!!)

I usually just blame PowerPoint (Brian's a Ppt MVP, after all), but I
see that now PowerPoint's been fixed, and we can all use it.

2 points I would like to add: -

a) In one of the earlier posts in PPT group I asked for project mgmt of PPT
automation using Excel and Brian asked a very important question as to the
base application (excel or PPT) im using for programming. I said excel
because I could pinch Jon's pre-built tutorials. I think one more important
needs to be added to the list which is as to whether one is creating objects
on the fly in PPT or whether one has a template in which one is populating
new data using Jon's latest method. The second would be extremely helpful
when the slides involve lot of custom/individual formatting differences
across objects (within slides/across slides). I think that way the if one
has a pre-built template the one doesnt have to "code" those custom
formatting within each individual sub. Less code, less debugging headache.

Definitely. My projects typically include one or more templates, with
all of the boilerplate built in. If I have a few options, I either make
a bigger template and remove what I don't need, or I make multiple
templates. If the project involves Excel and PowerPoint, I usually use
templates in XL and PPT, though the PowerPoint ones aren't really
templates, but rather preformatted presentations with the various slides
that will be needed. I guess before long we'll do it all with XML and XLS.

b) Jon if possible please add this new way of your to the web-site. Others
wont have to break their heads with PPT object dislocations.

I actually thought of this approach based on an email from another
person, after having followed this thread. It worked for him, and now it
works for you, and both of you are clamoring for more pages on my web
site. I'll see if my publisher wants to commission a few pages (oops,
that's me, so I guess I'm doing it on my own time).

I write the occasional article, though, and this is a pretty good topic
for such an article. As I said, in my "free time", which means September
or so. I'll be traveling then, and I can't do real work on the plane,
but I can often use the time for reports and articles.

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