Pasting Excel Chart into PowerPoint Slide

3
votes

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:\Users\xyz\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

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.

4
excelvbapowerpoint
@DavidZemens Nope .Chart.Export FileName:="C:\Users\xyz\Desktop\1.png, FilterName:="PNG" will work just fine. However in Excel 2007 SP3 the images of the charts produced using this method are of a really bad quality. For some reason when pasting it and saving from PowerPoint it's much better and hence the reason for my above approach. – CaptainABC
Try pptApp.CommandBars.ExecuteMso "PastePng" method instead of PasteSpecial. – David Zemens
@DavidZemens pptApp.CommandBars.ExecuteMso "PastePng" gives Method 'ExecuteMso' of object '_CommandBars' failed. Also it there a working way to paste the chart itself instead of pasting it as PNG? – CaptainABC
See THIS to paste chart directly. – David Zemens
@DavidZemens Thanks for the link; but trying pptApp.CommandBars.ExecuteMso "PasteExcelChartSourceFormatting" fails giving Method 'ExecuteMso' of object '_CommandBars' failed. Here is a link to a my sample sheet: db.tt/nGrgF5bA – CaptainABC

4 Answers

1
votes

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
0
votes

This is a common error we can experience when we copy information from one Office application to another. The best way I can describe it is that the program runs too fast and the information we copy never actually makes it into our clipboard.

This means that when we go and try to paste it, we get an error because there is nothing in our clipboard to paste.

Now lucky for us there is a way to fix this error but it requires us to add an extra line of code.

'Copy the chart
Set objChart = Worksheets("Sheet1").ChartObjects("Chart 1").Chart
    objChart.CopyPicture

'Pause the application for ONE SECOND
Application.Wait Now + #12:00:01 AM#

'Paste your content into a slide as the "Default Data Type"
Set pptSlide = pptPres.Slides.Add(1, ppLayoutBlank)
    pptSlide.Shapes.PasteSpecial DataType:=ppPasteDefault, Link:=msoFalse

Now all I did was add an extra line of code that pauses your Excel application for one second. This will give it enough time to make sure that the information is stored in your clipboard.

You might be asking the question why does this happen sometimes but then not other times. Well, it just boils down to this the clipboard can act unpredictably and clear out information inside of it.

That's why if we can avoid storing the information in the clipboard we try to. However, in this case, we can't avoid it so we just have to live with the unpredictability.

0
votes

@areed1192's answer might work if PowerPoint had an Application.Wait message, but that's an Excel thing.

I was able to do something similar by using a technique found here:

Which is to say, put a the top of the module:

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

And then call it like this:

Sleep 1000
DoEvents

(I'm not positive the DoEvents helped, but it seemed like it might be a good idea for resolving a race condition in VBA if that's what's going on.)

0
votes

sld.Shapes.PasteSpecial DataType:=0

or

sld.Shapes.PasteSpecial DataType:=ppPasteShape