Excel chart object from Powerpoint

[larindom]

Hi there,

First post so bear with me.

I am trying to edit the seriescollection of a chart (add labels to a scatter) through a macro written in Powerpoint. I have activated the Excel 14.0 reference library, but somehow I am not able to get to the seriescollection - which I am able to from Excel VBA.

My chart is called scatter and is located on the sheet called "SP-data Graphs". Book is the workbook.

My code looks like this:

Dim rowno As String
rowno = book.Sheets("Lean - PDCA").Cells(100000, 2).End(xlUp).Row

For x = 1 To rowno
    If book.Sheets("SP-data Graphs").Charts("scatter").SeriesCollection(1).Points(x).HasDataLabel = True Then
    Else
     book.Sheets("SP-data Graphs").Charts("scatter").SeriesCollection(1).Points(x).HasDataLabel = True
    End If

    book.Sheets("SP-data Graphs").Charts("scatter").SeriesCollection(1).Points(x).DataLabel.Text = book.Sheets("LEAN - PDCA").Cells(x, 1).Value
Next

I have tried dimming the chart, but that won't work either for some reason. In Excel VBA I dimmed graf as chart, then selected the chartobject (!) and set graf = activechart. I cannot choose activechart from Powerpoint for some reason though.

Please ask if you need clarification on something!

Brgds,
Lasse

 

[Worf]

Hi

Try something like this:

' at a PowerPoint module
Sub ExcelChart()
Dim xl As Object, wb As Excel.Workbook, sc As Excel.Series
Dim rowno%, x%, ch As ChartObject
On Error Resume Next
Set xl = GetObject(, "Excel.Application")
If Err.Number <> 0 Then Set xl = CreateObject("Excel.Application")
Err.Clear
On Error GoTo 0
xl.Visible = True
Set wb = xl.ActiveWorkbook
rowno = wb.Sheets("Lean - PDCA").Cells(100000, 2).End(xlUp).Row
Set ch = wb.Sheets("SP-data Graphs").ChartObjects("scatter")
Set sc = ch.Chart.SeriesCollection(1)
For x = 1 To rowno
    If sc.Points(x).HasDataLabel = True Then
    Else
     sc.Points(x).HasDataLabel = True
    End If
    sc.Points(x).DataLabel.Text = wb.Sheets("LEAN - PDCA").Cells(x, 1)
Next
End Sub

 

[larindom]

Awesome - thanks a million!

I did not copy your entire code, but the bit with
Set ch = wb.Sheets("SP-data Graphs").ChartObjects("scatter")
Set sc = ch.Chart.SeriesCollection(1)
was very helpful! In locals, I could not find any reference to the seriescollection, so I could not see how to capture it. Thx again!