CODEWITHSUNDEEP
vbaexcel
mongolol
mongolol

Reputation: 971

Error in script which copies charts from Excel to PowerPoint

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.)

Upvotes: 1

Views: 916

Answers (4)

Jon
Jon

Reputation: 91

I had a very similar problem with Microsoft Word - an intermittent issue with using VBA to copy Excel charts into a document as pictures. The accepted answer didn't quite work for me - it seemed to improve the situation but still not all charts copied correctly. What did work for me was to add a short "pause" by calling Timer in addition to the DoEvents call, which I wrapped in a Sub as follows:

Sub Pause(PauseTime As Double)
    Dim Start
    Start = Timer    ' Set start time.
    Do While Timer < Start + PauseTime
        DoEvents     ' Yield to other processes.
    Loop
End Sub

Per the accepted answer I then called Pause after copying the chart with a 0.2 second duration. And then called it again after the paste just in case! E.g:

Worksheets("SheetName").ChartObjects("ChartName").Chart.CopyPicture
Pause (0.2)
WordApp.Activate
WordApp.Selection.PasteSpecial Placement:=0, DisplayAsIcon:=False ' 0 = InLine
Pause (0.2)

Upvotes: 0

Jon Peltier
Jon Peltier

Reputation: 6063

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.

Upvotes: 2

Shai Rado
Shai Rado

Reputation: 33672

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.

Upvotes: 1

mongolol
mongolol

Reputation: 971

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

Upvotes: 2

Related Questions