Powerpoint chart data source - set with VBA

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

Select allOpen in new window

Regards

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")

Select allOpen in new window

It barfs at the chrt.SetSourceData line with runtime error 13, can't convert types (Typen unverträglich)

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"

Select allOpen in new window

Could you send a dummy

I have had to change the extension from .pptm to txt - so change it back.
zTEST.txt

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?

No Sorry

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!

I have actually tried this earlier:

    Dim rng As Range
    Set rng = oXL.Sheets("013").Range("A1:D12")
    chrt.SetSourceData Source:=rng

Select allOpen 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.

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

Select allOpen in new window

Thanks, I'll give it a try in (at least) a couple of hours time then get back.

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.

A follow on question is here:
https://www.experts-exchange.com/questions/28630736/Powerpoint-chart-Add-seriesCollection-item-display-problems.html