Get data from embedded Excel chart

[Techbert]

Hello Everyone,

Great to be able to finally post to this much used resource.

The issue at hand, I receive PowerPoint files with several embedded Excel charts, with no access to the source data. My task is to extract data from specific charts and compile it into a comparative Excel table. While I am able to get the series data points and compiling the data after retrieval is not an issue, my challenge has been to get the horizontal axis data point labels.

Sample Chart

Garbled image removed by Moderator

Sample Code

Sub test()

Dim sld As Slide
Dim shp As Shape
Dim valarray As Variant
For Each sld In ActivePresentation.Slides
For Each shp In sld.Shapes
If shp.HasChart Then
If shp.Chart.ChartType = 51 And InStr(LCase(shp.Chart.ChartTitle.Text), "mom") > 0 Then
Debug.Print sld.SlideNumber
Debug.Print shp.Chart.ChartTitle.Text & ": " & shp.Chart.ChartType
valarray = shp.Chart.SeriesCollection(2).Values
Debug.Print shp.Chart.SeriesCollection(2).Name
For i = 1 To UBound(valarray)
Debug.Print Round(valarray(i), 4)
Next i
End If

End If
Next shp
Debug.Print
Next sld
End Sub

The code does return the expected chart title and the second series (November) title and values. I have tried various iterations of Chart.SeriesCollection and Chart.Axes code as collections, but not been able to pull the location labels.

Any assistance would be appreciate.

 

[pgc01]

Hi
Welcome to the board

As you may know this is an excel not a ppt forum.

This code works for me when executed from excel.

See if this helps:

Sub PPTChartTest()
Dim pptApp As PowerPoint.Application
Dim pptPre As PowerPoint.Presentation
Dim pptSld As PowerPoint.Slide
Dim pptShp As PowerPoint.Shape
Dim pptCht As PowerPoint.Chart
Dim pptXAxisCategoryNames As Variant
Dim sFile As String
Dim vYValues As Variant
    
    sFile = "c:\tmp\pptchart1.pptx"

    Set pptApp = New PowerPoint.Application
    Set pptPre = pptApp.Presentations.Open(Filename:=sFile, ReadOnly:=msoFalse)

    For Each pptSld In pptPre.Slides
        For Each pptShp In pptSld.Shapes
            If pptShp.HasChart Then
                Set pptCht = pptShp.Chart
                If pptCht.ChartType = 51 And pptCht.HasTitle Then
                    If InStr(LCase(pptCht.ChartTitle.Text), "mom") > 0 Then
                        vYValues = pptCht.SeriesCollection(2).Values
                        pptXAxisCategoryNames = pptCht.Axes(Type:=xlCategory).CategoryNames
                        
                        ' debug messages
                        Debug.Print "Slide number: " & pptSld.SlideNumber
                        Debug.Print "Chart title - type: " & pptCht.ChartTitle.Text & " - " & pptCht.ChartType
                        Debug.Print "Series 2 name: " & pptCht.SeriesCollection(2).Name
                        Debug.Print "Series 2 Y values: " & Join(vYValues)
                        Debug.Print "X axis categories: " & Join(pptXAxisCategoryNames)
                        
                    End If
                End If
            End If
        Next pptShp
    Next pptSld
    
    pptPre.Close
    pptApp.Quit
End Sub

 

[pgc01]

P. S. You have to set the reference to the Microsoft PowerPoint object library.

 

[Techbert]

P. S. You have to set the reference to the Microsoft PowerPoint object library.

Thank you so very much, works great! CategoryNames was the property I was missing. I apologize for the errant posting.

 

[pgc01]

You're welcome. Thanks for the feedback.