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, Jul 11 '13 at 10:13
i think no one here has any idea how to replicate the steps in VBA. — sifar, Jul 13 '13 at 04:33
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, Jul 13 '13 at 09:57
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, Jul 17 '13 at 19:26
For point #3, try the `AddOLEObject` method [here](http://social.msdn.microsoft.com/Forums/office/en-US/09c91d34-ffb1-4f19-8627-70d98eb4ddd0/use-destination-theme-and-embed-workbook-option-with-vba) — David Zemens, Jul 28 '13 at 21:53
For point #4, try `ChtObj.Chart.ChartData.Activate` (or `ChtObj.ChartData.Activate` (I don't remember which is correct syntax and I am not able to test right now). — David Zemens, Jul 28 '13 at 21:54
Thanks for your reply David. Have tried your suggestions earlier, but doesn't seem to give me the desired results. Can you post your code here? — sifar, Aug 04 '13 at 15:59

score 0 · Answer 1 · answered Oct 05 '13 at 15:18

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 Answers1