Extracting chart title from Embedded Excel Chart in PowerPoint

[Barb Reinhardt]

I have the following snippet of code in function "FindGraphObjectTitle"

Function FindGraphObjectTitle(myShape As Shape) As String

Dim myOLEFormat As OLEFormat
Dim myGraphObject As Object
Dim myExcelObject As Excel.ChartObject

Set myOLEFormat = myShape.OLEFormat

FindGraphObjectTitle = "N/A"

If myOLEFormat.ProgID Like "MSGraph*" Then
FindGraphObjectTitle = "No MSGraph Title"
Set myGraphObject = myOLEFormat.Object
With myGraphObject
If .HasTitle Then
FindGraphObjectTitle = myGraphObject.ChartTitle.Text
End If
End With
ElseIf myOLEFormat.ProgID Like "Excel*" Then
FindGraphObjectTitle = "No Excel Title"

'Need your help here
End With

End If
End Function

I have no idea what to do with this. I can't seem to figure out the code
to get to the Excel chart . I know the code within excel to get the title,
but the OLEObject piece has me stumped.

Thanks,
Barb Reinhardt

 

[Andy Pope]

Hi,

This assumes no other workbooks are already open in the excel application.
If there are other workbooks open you will need to loop through the
Workbooks collection and identify the one within you presentation,

Function FindGraphObjectTitle(myShape As Shape) As String

Dim myOLEFormat As OLEFormat
Dim myGraphObject As Object
Dim myExcelObject As Excel.ChartObject

Set myOLEFormat = myShape.OLEFormat

FindGraphObjectTitle = "N/A"

If myOLEFormat.ProgID Like "MSGraph*" Then
FindGraphObjectTitle = "No MSGraph Title"
Set myGraphObject = myOLEFormat.Object
With myGraphObject
If .HasTitle Then
FindGraphObjectTitle = myGraphObject.ChartTitle.Text
End If
End With
ElseIf myOLEFormat.ProgID Like "Excel*" Then
FindGraphObjectTitle = "No Excel Title"
With myOLEFormat.Object.Application.Workbooks(1).Charts(1)
If .HasTitle Then
FindGraphObjectTitle = .ChartTitle.Text
End If
End With
End If
End Function

Cheers
Andy