Powerpoint chart data source - set with VBA

Say I have a powerpoint presentation with two slides.
Slide 1 has an excel object embedded in it - not linked.
Slide 2 has a chart.
What VBA code would I need to modify the data source of the chart to point to a range on the excel object on slide 1.

eg.
chart.data = slide(1).Excel.Sheet("Year2014").Range("A1:C12")
Or
chart.data = slide(1).Excel.Sheet("Year2013").Range("A21:C32")

The sheet name and range are not fixed.
LVL 45
AndyAinscowFreelance programmer / ConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

How do you want to determine the range?

pls try
Sub Macro()

Dim sld As PowerPoint.Slide
Dim shp As PowerPoint.Shape

Set sld = ActivePresentation.Slides(1)

For Each shp In sld.Shapes
    If shp.Type = msoEmbeddedOLEObject Then
        If shp.OLEFormat.ProgID Like "Excel*" Then Set oXL = shp.OLEFormat.Object
        With oXL.Sheets(1)
            LastRow = .UsedRange.Rows.Count
            LastCol = .UsedRange.Columns.Count
        End With
        MsgBox "Rows " & LastRow & " Columns " & LastCol
    End If
Next

End Sub

Open in new window

Regards
0
AndyAinscowFreelance programmer / ConsultantAuthor Commented:
The excel part is fine.  It is the chart data source that I haven't cracked.

I've got test code like this (hard coded values will later be replaced with variables, error trapping will be coded - this is just to get it working)
    Dim oshp As Shape, oXL As Object
    Set oshp = ActivePresentation.Slides(1).Shapes(1)
    Set oXL = oshp.OLEFormat.Object
    
    Dim chrt As Chart
    Set chrt = ActivePresentation.Slides(2).Shapes(3).Chart
    chrt.SetSourceData oXL.Sheets("013").Range("A1:D12")

Open in new window


It barfs at the chrt.SetSourceData line with runtime error 13, can't convert types (Typen unverträglich)
0
AndyAinscowFreelance programmer / ConsultantAuthor Commented:
ps.  This code does make a change in the embedded workbook - that is why I say the excel part is OK:
    Dim oshp As Shape, oXL As Object
    Set oshp = ActivePresentation.Slides(1).Shapes(1)
    Set oXL = oshp.OLEFormat.Object
    
oXL.Sheets("013").Range("A15") = "hello Excel"

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Rgonzo1971Commented:
Could you send a dummy
0
AndyAinscowFreelance programmer / ConsultantAuthor Commented:
I have had to change the extension from .pptm to txt - so change it back.
zTEST.txt
0
Rgonzo1971Commented:
Sorry can't help further
I fear it is not possible to do it
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AndyAinscowFreelance programmer / ConsultantAuthor Commented:
Wierd - one can link to external excel files (according to my understanding of the help) but not to an embedded excel object  (which would reduce chances of the source data getting damaged).


Thanks, but I've just seen something else.
I've tried to create a new powerpoint display, added a slide and clicked on insert a chart.  I get a dialog to choose which sort, I choose then click OK and the dialog just reappears - the only way I can get rid of it is with cancel, which results in no chart.  I've done a quick repair of the office installation and it stays the same.  (I can create a chart in excel then copy/paste into powerpoint - so it seems to be a powerpoint problem.)  Have you heard of this behaviour?
0
Rgonzo1971Commented:
No Sorry
0
Jamie GarrochPowerPoint Consultant & DeveloperCommented:
This is VERY odd. You're getting a mismatch error because PowerPoint thinks that the SetSourceData method is expecting a String and not a Range as you would in Excel. If you set a reference to the Excel library and then open the Object Browser in the PowerPoint VBE and search for SetSourceData, you get two matches:

Sub SetSourceData(Source As Range, [PlotBy])
    Member of Excel.Chart

Sub SetSourceData(Source As String, [PlotBy])
    Member of PowerPoint.Chart

I haven't figured out the best/correct way round this as yet but it looks like a PowerPoint library bug to me!
0
Jamie GarrochPowerPoint Consultant & DeveloperCommented:
Ah ha. In the PowerPoint model, the method is expecting a string which holds the address of the chart data range that contains the source data!

https://msdn.microsoft.com/en-us/library/office/ff746759.aspx?f=255&MSPPError=-2147217396

Same method operating in two different ways in two different MSO object models. Now that's clever Microsoft!
0
AndyAinscowFreelance programmer / ConsultantAuthor Commented:
I have actually tried this earlier:
    Dim rng As Range
    Set rng = oXL.Sheets("013").Range("A1:D12")
    chrt.SetSourceData Source:=rng

Open in new window

with a reference to excel but it barfs with exactly the same problem.  I just don't know how I could convert the range into a string.

I'm beginning to think the chart support in Powerpoint is ********************** (censored)

There was an old powerpoint display, I saved it as a new format pptx.  Problems with automating the charts.  By chance I found that converting to the new format still left the charts as the old type of object.  So I converted those to the new format.  The old automation code didn't work as the new charts were excel based for the data.  Corrected the code.  Now the automation still would crash intermittently - excel not responding.  Hence my idea to embed one excel doc on a slide which I would hide rather than 30+ charts each with its own excel workbook.  Can't change the path to the data in the chart.   Aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaargh.
0
Jamie GarrochPowerPoint Consultant & DeveloperCommented:
Totally agree Andy!

I tried a slightly different approach and this works with your example file. Note that I added the column headers back as row 1 in the source data on slide 1 so the range is A1:D13 now:

' Create a chart on a PowerPoint slide from the PowerPoint VBE
' Requires a reference to the Excel library in it's current form but could be modified for late binding
Sub CreateChartFromEmbeddedWorkbook()
    Dim oSrcDataShp As Shape, oWB1 As Object
    
    Set oSrcDataShp = ActivePresentation.Slides(1).Shapes(1)
    Set oWB1 = oSrcDataShp.OLEFormat.Object
    
    Dim oTgtChrtShp As Shape
    Set oTgtChrtShp = ActivePresentation.Slides(3).Shapes.AddChart(xl3DColumn)
   
    Dim oTgtChrt As Chart
    Set oTgtChrt = oTgtChrtShp.Chart

    Dim oWB2 As Excel.Workbook
    Dim oWS2 As Excel.Worksheet
   
    Set oWB2 = oTgtChrt.ChartData.Workbook
    Set oWS2 = oWB2.Worksheets(1)
    
    ' Resize the data table to match the source data
    oWS2.ListObjects("Table1").Resize oWS2.Range("A1:D13")
    
    Dim iRow As Integer, iCol As Integer
    For iRow = 1 To 13
      For iCol = 1 To 4
        ' Setting the whole range doesn't appear to work so do it cell by cell
        oWS2.Cells(iRow, iCol) = oWB1.Sheets("013").Cells(iRow, iCol)
      Next
    Next
    
    Set oSrcDataShp = Nothing: Set oTgtChrtShp = Nothing
    Set oWB1 = Nothing: Set oWB2 = Nothing: Set oWS2 = Nothing
    Set oTgtChrt = Nothing
End Sub

Open in new window

0
AndyAinscowFreelance programmer / ConsultantAuthor Commented:
Thanks, I'll give it a try in (at least) a couple of hours time then get back.
0
AndyAinscowFreelance programmer / ConsultantAuthor Commented:
Aaah.  That isn't linking but copying the data into a local workbook for the graph.  I'm currently doing that at present (from Access automating powerpoint) and running into problems with excel crashing as it is being opened/closed numerous times.  Hence my idea to just fill one embedded worksheet and *link* the individual charts to that one excel.

I'm having a go with a different approach using the SeriesCollection and, surprise, surprise, having more problems.  This is a particularly nasty greasy pole.
0
AndyAinscowFreelance programmer / ConsultantAuthor Commented:
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft PowerPoint

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.