Error in script which copies charts from Excel to PowerPoint

Question

I am attempting to call the below Sub in order to copy given chart to a specified PowerPoint presentation. However, when I run the macro which calls this Sub, the line indicated below returns the following error: "Object doesn't support this property or method." What's odd is that both Shapes and Slide do contain the methods which are called. As well, the bitmap is correctly copied to my clipboard and pastes into the slide before the error is called. You will find the Sub() below.

Sub copyChart(chrt As Chart, pres As PowerPoint.Presentation)
    Dim curSlide As Slide, dummySlide As Slide
    Set dummySlide= pres.Slides(2) 'Second slide is dummy slide.
    Set curSlide = dummySlide.Duplicate(1) 'Duplicate dummy, set as current slide.
    chrt.CopyPicture Appearance:=xlScreen, Format:=xlBitmap 'Copy the chart as a picture.
    curSlide.Shapes.Paste '<-----------Error here.
End Sub

As well, I was hoping to provide a .txt file of my entire script, but was unsure how (it is a little lengthy to paste here). Thanks for your help.

(Note that this implementation is very similar to that at Paste Excel Chart into Powerpoint using VBA, further confusing me.)

Answer 1

I have had a lot of trouble in recent versions of Office. 2003 and earlier didn't have this problem, 2007 and 2010 had it a bit, and 2013 and 2016 have it in spades.

When you step through the code it works fine, but when you run it at full speed, it errors on the paste.

It's as if the copy doesn't have time to finish finish, so when you paste the clipboard doesn't have anything in it yet to paste.

Sometimes this helps:

chrt.CopyPicture Appearance:=xlScreen, Format:=xlBitmap
DoEvents
curSlide.Shapes.Paste

DoEvents tells VBA to wait while background operations have a chance to finish up.

Answer 2

It looks like the error can be attributed to how VBA handles variables across different references. (In particular, how PPT VBA handles them.) I was able to get the macro to work by actively selecting/copying the charts. I will need to do a little more research to get why variables cause problems, but at least I know how tackle the problem.

Sub copyChart(curSlide As Slide)
    Dim chr as ChartObject
    Set chr = Sheets("CHARTSHEET").ChartObjects(1)  
    Sheets("CHARTSHEET").Select
    ActiveChart.CopyPicture
    curSlide.Shapes.PasteSpecial
End Sub

Answer 3

I like to use another method, I like to define an Object, then set it to the pasted Chart. Afterwards, it's much easier modifying the pasted Chart object's parameters inside PowerPoint (from Excel).

See code below:

Sub copyChart(curSlide As Slide)

Dim chr             As ChartObject
Dim myChart         As Object

Set chr = Sheets("CHARTSHEET").ChartObjects(1)
chr.Copy

' setting myChart object to the pasted chart (let's me later an easy way to modify it's parameters)   
Set myChart = curSlide.Shapes.PasteSpecial(ppPasteBitmap, msoFalse) ' can change first parameter to other avaialabe formats : ppPasteGIF, ppPasteEnhancedMetafile, ppPasteOLEObject, etc.

' set different parameters for the pasted chart in PowerPoint slide
With myChart
    .Left = 200
    .Top = 200
End With

End Sub

In the code line:

Set myChart = curSlide.Shapes.PasteSpecial(ppPasteBitmap, msoFalse)

You can change the first parameter in brackets: ppPasteBitmap to many other avaialble formats (test them and see which one gives you the best result), such as: ppPasteGIF, ppPasteEnhancedMetafile, ppPasteOLEObject, etc.