vba - Pasting Excel Chart into PowerPoint Slide

Question

The below Sub is supposed to paste an Excel chart into a newly created PowerPoint slide. It then exports the chart as a PNG:

Sub ChartsToPowerPoint()

    Dim pptApp As PowerPoint.Application
    Dim pptPres As PowerPoint.Presentation
    Dim pptSlide As PowerPoint.Slide

    'Open PowerPoint and create an invisible new presentation.
    Set pptApp = New PowerPoint.Application
    Set pptPres = pptApp.Presentations.Add(msoFalse)

    'Set the charts and copy them to a new ppt slide

    Set objChart = Worksheets("Sheet1").ChartObjects("Chart 1").Chart
    objChart.ChartArea.Copy
    Set pptSlide = pptPres.Slides.Add(1, ppLayoutBlank)
    pptSlide.Shapes.PasteSpecial DataType:=ppPasteDefault, Link:=msoFalse

    'Save Images as png
    path = "C:UsersxyzDesktop"

    For j = 1 To pptSlide.Shapes.Count
        With pptSlide.Shapes(j)
            .Export path & j & ".png", ppShapeFormatPNG
        End With
    Next j

    pptApp.Quit

    Set pptSlide = Nothing
    Set pptPres = Nothing
    Set pptApp = Nothing

End Sub

I get a Run-time error:

Shapes (unknown member): Invalid request. Clipboard is empty or contains data which may not be pasted here.

At the line:

pptSlide.Shapes.PasteSpecial DataType:=ppPasteDefault, Link:=msoFalse

Error http://im64.gulfup.com/pZNwxJ.png

I tried pptSlide.Shapes.Paste but it gives the same error.

When I amend pptApp.Presentations.Add(msoFalse) to pptApp.Presentations.Add only it works but the PowerPoint App is displayed.

When I change to .PasteSpecial DataType:=ppPasteEnhancedMetafile or .PasteSpecial DataType:=ppPastePNG everything runs smoothly even with .Add(msoFalse).

I am thinking it might be something to do with setting the focus or so.

Answer 1

PasteSpecial and CommandBars.ExecuteMso should both work (tested your code in Excel/PowerPoint 2010 with the following caveat:

When you add presentation, you have to open it WithWindow:=True

Set pptPres = pptApp.Presentations.Add(msoCTrue)

I did some more digging, you need to use the CopyPicture method and then I think you can open withwindow=False. Try:

Sub ChartsToPowerPoint()

    Dim pptApp As PowerPoint.Application
    Dim pptPres As PowerPoint.Presentation
    Dim pptSlide As PowerPoint.Slide
    Dim objChart As Chart

    'Open PowerPoint and create an invisible new presentation.
    Set pptApp = New PowerPoint.Application
    Set pptPres = pptApp.Presentations.Add(msoFalse)

    Set objChart = Worksheets("Sheet1").ChartObjects("Chart 1").Chart
    objChart.CopyPicture

    Set pptSlide = pptPres.Slides.Add(1, ppLayoutBlank)
    pptSlide.Shapes.PasteSpecial DataType:=ppPasteDefault, Link:=msoFalse

    'Save Images as png
    Path = CreateObject("Wscript.Shell").SpecialFolders("Desktop") & ""

    For j = 1 To pptSlide.Shapes.Count
        With pptSlide.Shapes(j)
        .Export Path & j & ".png", ppShapeFormatPNG
        End With
    Next j

    pptApp.Quit

    Set pptSlide = Nothing
    Set pptPres = Nothing

    Set pptApp = Nothing

End Sub