Accessing an Embedded Excel Shape Data from VBA

Question

I've been having some trouble accessing the workbook object from an embedded excel worksheet in PowerPoint.

I've found a number of posts that recommend accessing the workbook properties and methods through ChartData.Activate. When I try to access the Chart property from the embedded table's Shape object, I get an error:

Run-time error '-2147024809 (80070057)'

This member can only be accessed for a Chart object

I have distilled down my code to the basic pieces that still create the error as an example:

Sub InsertToEmbeddedExcelTable()

    Dim xlTable As Shape

    Dim pp_chart as Chart

    'Name of embedded table is 'Object 3'

    Set xlTable = ActivePresentation.Slides(1).Shapes("Object 3")

    Set pp_chart = xlTable.Chart 'Here is where the issue is.

    Set pp_data = pp_chart.ChartData

    pp_data.Activate

    Set pp_workbook = pp_data.Workbook

    pp_workbook.Sheets("Sheet1").Cells(1, 2) = 5

End Sub

I have added both the Excel and PowerPoint Object Libraries to my references. How can I get access to the embedded workbook if it is not a chart? Thanks.

Answer 1

ChartDataSheet gives you a reference to the data sheet behind a chart that you created in PowerPoint.  It LOOKS and mostly ACTs a lot like an Embedded Excel chart but it's not quite the same.

Have a play with something like this.  You'll have to set a reference to whatever version of Excel you're working with (aka Early Binding).  You can convert to Late Binding once you have it working and don't need help from Intellisense, if you like.

Sub EmbeddedWorksheet()

    Dim oWs As Excel.Worksheet

    With ActiveWindow.Selection.ShapeRange(1)

        Set oWs = .OLEFormat.Object.Worksheets(1)

        With oWs

            .Cells(1, 2) = "I changed it back!"

        End With

    End With

End Sub

Answer 2

Thank you for that. Based on my reading, I thought the OLEFormat properties were no longer supported, but your solution works. Thank you very much.