Updating Excel OLE chart data in PPT from Excel

Hi,

I posted in PPT group but would like to try here as well.

Im trying to automatically update a Excel OLE CHART object Data in a
Powerpoint slide.

The name of the chart object in PPT slide is "NRMAWC023". It has an excel
chartsheet with name "Chart1" and a
worksheet "q20". The chart sheet named "chart1" is the one which gets
displayed in
PPT and the worksheet "q20" contains the data for "chart1". I run the code
at the end of the mail.

The data in the object NRMAWC023 gets updated (presently it has dummy data)
but then the active view in
excel OLE changes from "chart1" to "q20". Also, the whole object gets
RESIZED and REPositioned.

Thus automation defeats the ONE of the main purpose of why I want to
automate, which is to avoid embedded Excel content in PPT from resizing and
repositioning itself. (another reason why am automating is there is a lot
of slides on which this is to be done)

Please tell me what code I should write/include in the exiting code so that
the existing object in PPT doesnt get resized/repositioned and also the
ACTIVE VIEW
remains at "chart1".

Please guide me for the same.

Here is the novice macro for the same.

'below macro run from personal.xls workbook.

Option Explicit
Dim oPPTApp1 As PowerPoint.Application
Dim oPPTShape1 As PowerPoint.Shape
Dim rngNewRange1 As Excel.Range
Dim oExceldata As Object
Dim Excelwksheet As Worksheet

Sub UpdateExcelData()

Set oPPTApp1 = CreateObject("PowerPoint.Application")

oPPTApp1.Visible = msoTrue

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

If oPPTShape1.Name = "NRMAWC023" Then

Set oExceldata = oPPTShape1.OLEFormat.Object

Set rngNewRange1 = ActiveSheet.Range("A10:ag13")
rngNewRange1.Select
rngNewRange1.Copy

Set Excelwksheet = oExceldata.Worksheets("q20")
Excelwksheet.Range("A9").PasteSpecial xlPasteValues

Next oPPTShape1

End With

End Sub


Thanks a lot,
Hari
India

Hi,

I kept my Windows of PPT slide, Excel workbook and Excel Vb editior open in
such a manner that I could see all of them displayed in the screen.

Then I stepped through the code using F8.

I see that the resizing and repositioning of OLE chart in PPT takes place at
the stage when the yellow debugging indicator line moves to --> Set
Excelwksheet = oExceldata.Worksheets("q20")

I hope that is of some help for determining the cause of the problem.

Thanks a lot,
Hari
India

"Hari Prasadh" <[email protected]> wrote in message
news:%[email protected]...
> Hi,
>
> I posted in PPT group but would like to try here as well.
>
> Im trying to automatically update a Excel OLE CHART object Data in a
> Powerpoint slide.
>
> The name of the chart object in PPT slide is "NRMAWC023". It has an excel
> chartsheet with name "Chart1" and a
> worksheet "q20". The chart sheet named "chart1" is the one which gets
> displayed in
> PPT and the worksheet "q20" contains the data for "chart1". I run the code
> at the end of the mail.
>
> The data in the object NRMAWC023 gets updated (presently it has dummy
> data) but then the active view in
> excel OLE changes from "chart1" to "q20". Also, the whole object gets
> RESIZED and REPositioned.
>
> Thus automation defeats the ONE of the main purpose of why I want to
> automate, which is to avoid embedded Excel content in PPT from resizing
> and repositioning itself. (another reason why am automating is there is a
> lot of slides on which this is to be done)
>
> Please tell me what code I should write/include in the exiting code so
> that
> the existing object in PPT doesnt get resized/repositioned and also the
> ACTIVE VIEW
> remains at "chart1".
>
> Please guide me for the same.
>
> Here is the novice macro for the same.
>
> 'below macro run from personal.xls workbook.
>
> Option Explicit
> Dim oPPTApp1 As PowerPoint.Application
> Dim oPPTShape1 As PowerPoint.Shape
> Dim rngNewRange1 As Excel.Range
> Dim oExceldata As Object
> Dim Excelwksheet As Worksheet
>
> Sub UpdateExcelData()
>
> Set oPPTApp1 = CreateObject("PowerPoint.Application")
>
> oPPTApp1.Visible = msoTrue
>
> With oPPTApp1.ActivePresentation.Slides(1)
> For Each oPPTShape1 In .Shapes
>
> If oPPTShape1.Name = "NRMAWC023" Then
>
> Set oExceldata = oPPTShape1.OLEFormat.Object
>
> Set rngNewRange1 = ActiveSheet.Range("A10:ag13")
> rngNewRange1.Select
> rngNewRange1.Copy
>
> Set Excelwksheet = oExceldata.Worksheets("q20")
> Excelwksheet.Range("A9").PasteSpecial xlPasteValues
>
> Next oPPTShape1
>
> End With
>
> End Sub
>
>
> Thanks a lot,
> Hari
> India
>

In the decade or two that I've been using XL and PP I cannot recall a
single instance where I needed to use code to update the PP copy of an
XL chart -- unless, of course, the update has to happen during a
slideshow. Just copy the chart in XL, and in PP use Edit | Paste
Special... | check the 'paste link' (or 'maintain link' or whatever it
is called) option.

If you must use code, search the google.com archives of the PP
newsgroup. I have posted code on a few occassions on how to update a
XL chart / range shown in a PP file while a slideshow is running. Of
course, I am sure others, such as Steve Rindsberg and Shyam Pillai,
must have done the same and/or have examples on their respective
websites.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <#[email protected]>,
[email protected] says...
> Hi,
>
> I posted in PPT group but would like to try here as well.
>
> Im trying to automatically update a Excel OLE CHART object Data in a
> Powerpoint slide.
>
> The name of the chart object in PPT slide is "NRMAWC023". It has an excel
> chartsheet with name "Chart1" and a
> worksheet "q20". The chart sheet named "chart1" is the one which gets
> displayed in
> PPT and the worksheet "q20" contains the data for "chart1". I run the code
> at the end of the mail.
>
> The data in the object NRMAWC023 gets updated (presently it has dummy data)
> but then the active view in
> excel OLE changes from "chart1" to "q20". Also, the whole object gets
> RESIZED and REPositioned.
>
> Thus automation defeats the ONE of the main purpose of why I want to
> automate, which is to avoid embedded Excel content in PPT from resizing and
> repositioning itself. (another reason why am automating is there is a lot
> of slides on which this is to be done)
>
> Please tell me what code I should write/include in the exiting code so that
> the existing object in PPT doesnt get resized/repositioned and also the
> ACTIVE VIEW
> remains at "chart1".
>
> Please guide me for the same.
>
> Here is the novice macro for the same.
>
> 'below macro run from personal.xls workbook.
>
> Option Explicit
> Dim oPPTApp1 As PowerPoint.Application
> Dim oPPTShape1 As PowerPoint.Shape
> Dim rngNewRange1 As Excel.Range
> Dim oExceldata As Object
> Dim Excelwksheet As Worksheet
>
> Sub UpdateExcelData()
>
> Set oPPTApp1 = CreateObject("PowerPoint.Application")
>
> oPPTApp1.Visible = msoTrue
>
> With oPPTApp1.ActivePresentation.Slides(1)
> For Each oPPTShape1 In .Shapes
>
> If oPPTShape1.Name = "NRMAWC023" Then
>
> Set oExceldata = oPPTShape1.OLEFormat.Object
>
> Set rngNewRange1 = ActiveSheet.Range("A10:ag13")
> rngNewRange1.Select
> rngNewRange1.Copy
>
> Set Excelwksheet = oExceldata.Worksheets("q20")
> Excelwksheet.Range("A9").PasteSpecial xlPasteValues
>
> Next oPPTShape1
>
> End With
>
> End Sub
>
>
> Thanks a lot,
> Hari
> India
>
>
>

Hi Tushar,

Im coding in Excel and transferring data to PPT.

When I update Excel worksheets or MS graph applets using code I have no
problem regarding objects getting resized/repositioned arbitrarily.

But in case of charts the objects get distorted.

Please note I have to use code to automate new data pasting as I have a
template which am using to create lots (really lots) of different PPT
reports only by changing the source data to be pasted. That is why I cannot
use paste link method for the same.

Please guide me as to how I can avoid shrinking of charts

Thanks a lot,
Hari
India


"Tushar Mehta" <[email protected]> wrote in message
news:[email protected]...
> In the decade or two that I've been using XL and PP I cannot recall a
> single instance where I needed to use code to update the PP copy of an
> XL chart -- unless, of course, the update has to happen during a
> slideshow. Just copy the chart in XL, and in PP use Edit | Paste
> Special... | check the 'paste link' (or 'maintain link' or whatever it
> is called) option.
>
> If you must use code, search the google.com archives of the PP
> newsgroup. I have posted code on a few occassions on how to update a
> XL chart / range shown in a PP file while a slideshow is running. Of
> course, I am sure others, such as Steve Rindsberg and Shyam Pillai,
> must have done the same and/or have examples on their respective
> websites.
>
> --
> Regards,
>
> Tushar Mehta
> www.tushar-mehta.com
> Excel, PowerPoint, and VBA add-ins, tutorials
> Custom MS Office productivity solutions
>
> In article <#[email protected]>,
> [email protected] says...
>> Hi,
>>
>> I posted in PPT group but would like to try here as well.
>>
>> Im trying to automatically update a Excel OLE CHART object Data in a
>> Powerpoint slide.
>>
>> The name of the chart object in PPT slide is "NRMAWC023". It has an excel
>> chartsheet with name "Chart1" and a
>> worksheet "q20". The chart sheet named "chart1" is the one which gets
>> displayed in
>> PPT and the worksheet "q20" contains the data for "chart1". I run the
>> code
>> at the end of the mail.
>>
>> The data in the object NRMAWC023 gets updated (presently it has dummy
>> data)
>> but then the active view in
>> excel OLE changes from "chart1" to "q20". Also, the whole object gets
>> RESIZED and REPositioned.
>>
>> Thus automation defeats the ONE of the main purpose of why I want to
>> automate, which is to avoid embedded Excel content in PPT from resizing
>> and
>> repositioning itself. (another reason why am automating is there is a
>> lot
>> of slides on which this is to be done)
>>
>> Please tell me what code I should write/include in the exiting code so
>> that
>> the existing object in PPT doesnt get resized/repositioned and also the
>> ACTIVE VIEW
>> remains at "chart1".
>>
>> Please guide me for the same.
>>
>> Here is the novice macro for the same.
>>
>> 'below macro run from personal.xls workbook.
>>
>> Option Explicit
>> Dim oPPTApp1 As PowerPoint.Application
>> Dim oPPTShape1 As PowerPoint.Shape
>> Dim rngNewRange1 As Excel.Range
>> Dim oExceldata As Object
>> Dim Excelwksheet As Worksheet
>>
>> Sub UpdateExcelData()
>>
>> Set oPPTApp1 = CreateObject("PowerPoint.Application")
>>
>> oPPTApp1.Visible = msoTrue
>>
>> With oPPTApp1.ActivePresentation.Slides(1)
>> For Each oPPTShape1 In .Shapes
>>
>> If oPPTShape1.Name = "NRMAWC023" Then
>>
>> Set oExceldata = oPPTShape1.OLEFormat.Object
>>
>> Set rngNewRange1 = ActiveSheet.Range("A10:ag13")
>> rngNewRange1.Select
>> rngNewRange1.Copy
>>
>> Set Excelwksheet = oExceldata.Worksheets("q20")
>> Excelwksheet.Range("A9").PasteSpecial xlPasteValues
>>
>> Next oPPTShape1
>>
>> End With
>>
>> End Sub
>>
>>
>> Thanks a lot,
>> Hari
>> India
>>
>>
>>

Hari -

Why not just use VBA to create the chart in Excel, and paste the chart,
better yet a copy of it, into the slide? I do this all the time. It's
much more reliable, and I see no need to carry the baggage of a chart
and worksheet of an Excel OLE object within the slide.

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


Hari Prasadh wrote:

> Hi,
>
> I posted in PPT group but would like to try here as well.
>
> Im trying to automatically update a Excel OLE CHART object Data in a
> Powerpoint slide.
>
> The name of the chart object in PPT slide is "NRMAWC023". It has an excel
> chartsheet with name "Chart1" and a
> worksheet "q20". The chart sheet named "chart1" is the one which gets
> displayed in
> PPT and the worksheet "q20" contains the data for "chart1". I run the code
> at the end of the mail.
>
> The data in the object NRMAWC023 gets updated (presently it has dummy data)
> but then the active view in
> excel OLE changes from "chart1" to "q20". Also, the whole object gets
> RESIZED and REPositioned.
>
> Thus automation defeats the ONE of the main purpose of why I want to
> automate, which is to avoid embedded Excel content in PPT from resizing and
> repositioning itself. (another reason why am automating is there is a lot
> of slides on which this is to be done)
>
> Please tell me what code I should write/include in the exiting code so that
> the existing object in PPT doesnt get resized/repositioned and also the
> ACTIVE VIEW
> remains at "chart1".
>
> Please guide me for the same.
>
> Here is the novice macro for the same.
>
> 'below macro run from personal.xls workbook.
>
> Option Explicit
> Dim oPPTApp1 As PowerPoint.Application
> Dim oPPTShape1 As PowerPoint.Shape
> Dim rngNewRange1 As Excel.Range
> Dim oExceldata As Object
> Dim Excelwksheet As Worksheet
>
> Sub UpdateExcelData()
>
> Set oPPTApp1 = CreateObject("PowerPoint.Application")
>
> oPPTApp1.Visible = msoTrue
>
> With oPPTApp1.ActivePresentation.Slides(1)
> For Each oPPTShape1 In .Shapes
>
> If oPPTShape1.Name = "NRMAWC023" Then
>
> Set oExceldata = oPPTShape1.OLEFormat.Object
>
> Set rngNewRange1 = ActiveSheet.Range("A10:ag13")
> rngNewRange1.Select
> rngNewRange1.Copy
>
> Set Excelwksheet = oExceldata.Worksheets("q20")
> Excelwksheet.Range("A9").PasteSpecial xlPasteValues
>
> Next oPPTShape1
>
> End With
>
> End Sub
>
>
> Thanks a lot,
> Hari
> India
>
>

Hi Jon,

Thanks for jumping in!!

Actually the charts which I have in PPT are customized way too much and
there are scores (and scores) of slides and each slides has different KIND
of chart( starting from stacked bar with average line to pie charts etc) and
every data series chart has its own color/major axis specification and what
not.

Moreover I would be using this template to create lots and lots of different
reports running close to a thousand (only by changing the data sets which is
different for each object and each slide and each report).

So, creating charts in Excel and transferring to PPT is not an option for
me.

Hence, for me template offers some kind of base by which I may work further.

But the present problem has got me stumped.

I see that way I can overcome this problem is recording the position of Top,
left, Width and Height before pasting new data and then applying these same
positions on the DISLOCATED object after pasting. - this would solve my
problem of dislocation to some extent. (some extent only because this new
method is now causing dislocation of unrelated objects on the same slide?)

Also I think that the solution to the problem may lie in using Lock aspect
ratio or Auto scale feature in object format/graphs options, though not
sure.

Any thoughts?

I found the solution to the problem of view changing from chart to data by
setting a handle to the chart sheet and then doing activate chart sheet and
then using refresh option (or the xlsheet visible = 1).

I thought that activate chart sheet method alone should have worked well.
but it did not? Any better solutions?

Thanks a lot,
Hari
India

"Jon Peltier" <[email protected]> wrote in message
news:[email protected]...
> Hari -
>
> Why not just use VBA to create the chart in Excel, and paste the chart,
> better yet a copy of it, into the slide? I do this all the time. It's much
> more reliable, and I see no need to carry the baggage of a chart and
> worksheet of an Excel OLE object within the slide.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
>
>