Use Destination Theme & Embed Workbook (h)

Question

I am trying to copy a ChartObject created in a workbook sheet and paste it into a powerpoint 2010 slide, such that it becomes a standalone Excel Chart with data (embedded).

When i do it manually i.e :

Copy ChartObject present in WorkSheet
Goto Powerpoint Slide
Click PasteSpecial & select "Use Destination Theme & Embed Workbook (H)".
Right-click the ChartObject in Powerpoint & click "Edit Data".

Then i get a new standalone workbook with Title "Chart in Microsoft Excel", that shows the Chart as well as data. I have not been able to replicate this scenario where i get a new standalone workbook with Title "Chart in Microsoft Excel" using Excel VBA. Can someone help?

Assuming there is a simple column chart on the sheet, here is the code:

Option Explicit
Sub doit()
Dim Temp As Workbook
Dim Rng As Range
Dim ChtObj As ChartObject

With ThisWorkbook.Sheets(1)
    Set ChtObj = .ChartObjects(1)
    With ChtObj
        .Copy
    End With

    Dim ppapp As Object
    Dim pppres As Object
    Dim ppslide As Object

    On Error Resume Next
    Set ppapp = GetObject(, "Powerpoint.Application")

    If ppapp Is Nothing Then
        Set ppapp = CreateObject("Powerpoint.Application")
    End If
    On Error GoTo 0

    Set pppres = ppapp.presentations.Add
    Set ppslide = pppres.Slides.Add(1, 12)

    With ppapp
        .Activate
        .Visible = msoTrue
        .ActiveWindow.viewtype = 1
    End With

    ChtObj.Copy
    ppapp.ActiveWindow.View.Paste

End With
End Sub

KazJaw, i edited my post. Please review above. i have tried : ppslide.Shapes.PasteSpecial(11, 0, , , , 0) '11 = ppPasteShape OR ppslide.Shapes.PasteSpecial 10, 0, , , , 0 '10 = ppPasteOLEObject OR ppapp.ActiveWindow.View.PasteSpecial 10, 0, , , , 0 but it did not help! — sifar
i think no one here has any idea how to replicate the steps in VBA. — sifar
I think there are few people here who know how to do it. But you don't encourage to give you support- you don't vote here in SO, you don't approve answers. back to your question- one tip- search for .pastespecial to get you to the point no. 3. Point no. 4 is not replicable- you can make workbook edition by VBA therefore you don't need to simulate right-click in such situation. — Kazimierz Jawor
Kazjaw, i would certainly vote if it was clear where to vote. Let me know where i need to click vote. As for point 3, pastespecial in VBA does not seem to have that option "Use Destination Theme & Embed Workbook (H)". As for point 4, i am not trying to replicate right-click, but just trying to show by right-clicking how differently the chartobject opens up in a new standalone excel window, with its Title "Chart in Microsoft Excel". my question is - can this be replicated via VBA? — sifar

Rachel Hettinger Rachel Hettinger · Accepted Answer · 2013-10-05T15:18:23

There appears to be no corresponding method in the PowerPoint object model.. The only way to do this is to call the ribbon button itself:

Application.CommandBars.ExecuteMso("PasteExcelChartDestinationTheme")

BTW: To find the list of ribbon buttons, search for "Office 2010 Control IDs".

Use Destination Theme & Embed Workbook (h)

1 Answers