VBA PPT copy/paste chart inconsistent

Question

I'm slowly getting crazy because of this problem. I'm creating a powerpoint presentation from an excel workbook, where data needs to be filled in. I'm creating multiple slides already with no issues and tackled most problems already.

One of the final things for me to do is copy a chart from excel and pasting it in my ppt. This has worked before, but suddenly it just breaks, it doesnt want to paste the chart anymore.

In my main module I call sub ROI with some required data to continue

Call ROI(PPPRes, Slidestart, language, i)

This is in a seperate Module to keep things clean in the main module

Sub ROI(PPPRes, Slidenumber, language, proposal)
Set pp = CreateObject("PowerPoint.Application")
Dim oPPTShape As PowerPoint.Shape
Dim PPSlide As PowerPoint.Slide
Dim ColumnWidthArray As Variant
Dim i As Integer

'Create a slide on Slidenumber location
Set PPSlide = PPPRes.Slides.Add(Slidenumber, ppLayoutTitleOnly)
PPSlide.Select
PPSlide.Shapes("Title 1").TextFrame.TextRange.Text = Range("Titlename in chosen language")
PPSlide.Shapes.AddTable(3, 3).Select
Set oPPTShape = PPSlide.Shapes("Table 4")

'Filling in data in the table from an excel table. Basic stuff working with a few loops to make this happen


        'Changing the width of the created table, column by column
        ColumnWidthArray = Array(37, 210, 180)
        Set oPPTShape = PPSlide.Shapes("Table 4")
        On Error Resume Next
        With oPPTShape
            For i = 1 To 3
                .table.columns(i).width = ColumnWidthArray(i - 1)
            Next i
            .Top = 180
            .Left = 520
            .height = 200
        End With

        'Add a rectangle on the slide
        PPSlide.Shapes.AddShape Type:=msoShapeRectangle, Left:=404, Top:=400, width:=153, height:=43

        'Copy a picture from excel and paste it in the active slide   
        Sheets("Shapes").Shapes("ROI_img").Copy
        PPSlide.Shapes.Paste.Select
        pp.ActiveWindow.Selection.ShapeRange.Left = 800
        pp.ActiveWindow.Selection.ShapeRange.Top = 20

'Copy chart from excel (with index number that is linked to "proposal") and then paste onto slide
Sheets("Proposals").Shapes("ChartInvProp" & proposal).Copy
PPSlide.Shapes.Paste.Select
Set oPPTShape = PPSlide.Shapes("ChartInvProp" & proposal)
With PPSlide.Shapes("ChartInvProp" & proposal)
    .Left = 20
    .Top = 120
    .width = 480
    .height = 320
End With
end sub

So everything in the code is executed, but most of the time the chart from excel is NOT being pasted onto the slide. However, if I checked what is in the clipboard by breaking the code just after copying the chart from excel. And I then manually paste whatever is in the clipboard into a Word document I will see the chart. --> The action of copying the chart is being executed, but not the pasting part If I now continue after the break, the chart will be pasted on the powerpoint somehow. But if I do NOT break the code, and let it run its course, the chart will not be pasted.

Somehow it seems to need more time after copy before it can actually paste the chart. I dont really understand what is happening here. Sometimes it only pastes Chart 1 in the slide, and when it loops for the second/third/etc... chart it doesnt want to paste it anymore.

It really is very random, and I only see a little bit of structure in it not executing...

I know your pain with pasting charts. The workaround I found was to create a template of the presentation and update the data behind the chart that already exists in the template rather than paste a new chart in. Would that work for you? — Darren Bartrup-Cook
Why do you have .Paste.Select instead of just .Paste? Also you set On Error Resume Next much further up the code - have you tried adding On Error GoTo 0 so that VBA will tell you what the error is? — Chronocidal
@DarrenBartrup-Cook It works if I paste the chart first before I create a table in the slide. So for now it works and have not encountered charts not being copied in my presentation (fingers crossed). — Justanewbie
@Chronocidal Hmm, probably force of habit. Will change it, but it does not have any impact I guess? Have not tried that On Error Goto 0 yet, will have a look. But the On Error Resume Next is only valid for changing the width of the columns, that is to make sure in case the # width values in the array doesn't match the # of columns, but I guess in this case it can be removed. — Justanewbie
The problem "came back" - It is still skipping the copy past of the chart from time to time. Still not closer to finding out why this is happening. — Justanewbie

Justanewbie Justanewbie · Accepted Answer · 2018-08-09T08:42:38

This was the solution, using a 'DoEvents' between copy and pasting. This issue only occurred with Charts made in Excel, if I made the charts into pictures it worked without a problem. But copy/pasting a chart from Excel apparently takes more processing time and was slower than the program run speed. So it would skip from time to time.

Sheets("Proposals").Shapes("ChartInvProp" & proposal).Copy
DoEvents
PPSlide.Shapes.Paste.Select

Got the answer from:

Error in script which copies charts from Excel to PowerPoint

VBA PPT copy/paste chart inconsistent

1 Answers