Error in Code to Paste / Copy Shapes from Excel to PowerPoint VBA

Question

I created a code in VBA to copy shapes from a sheet in Excel to an existing PowerPoint file, these shapes are added to existing slides. In Excel I have an table that indicates to which slide and in which position to pase each shape (I named all shapes so that I can reference them by name). My code loops through this table to get the shape and paste it to the designated slide. This is my code:

Dim nombrePic As String, auxi As String, i As Integer
Dim PPT As Object 
Dim PPSlide As PowerPoint.Slide
Dim numSlide As Integer, posVertical As Double, posHorizontal As Double

'Abrir PPT
Set PPT = CreateObject("PowerPoint.Application")
PPT.Visible = True
PPT.Presentations.Open "c:\prueba.pptx"

auxi = Sheets("FigurasResumen_RangPD").Cells(2, 5)
i = 2

Do
    Sheets("FigurasResumen_RangPD").Shapes(auxi).Copy

    'Get paste information about shape
    numSlide = Sheets("FigurasResumen_RangPD").Cells(i, 8)
    posHorizontal = Sheets("FigurasResumen_RangPD").Cells(i, 9)
    posVertical = Sheets("FigurasResumen_RangPD").Cells(i, 10)

    PPT.ActiveWindow.View.GotoSlide (numSlide)
    PPT.ActiveWindow.View.PasteSpecial DataType:=ppPasteDefault

    PPT.ActiveWindow.Selection.ShapeRange.Left = Format(Application.CentimetersToPoints(posHorizontal), "0.00")
    PPT.ActiveWindow.Selection.ShapeRange.Top = Format(Application.CentimetersToPoints(posVertical), "0.00")
    PPT.ActiveWindow.Selection.ShapeRange.ZOrder msoSendToBack

    i = i + 1
    auxi = Sheets("FigurasResumen_RangPD").Cells(i, 5)
Loop Until auxi = ""

End Sub

When I first tried it, it work fine. I created a sample PPT and everything went great. Once I changed the path to use the official PPT (which weights around 120MB) it shows the following error: View.PasteSpecial : Invalid Request. Clipboard is empty or contains data which may not be pasted here.

Any ideas why this happens?

mathguy mathguy · Accepted Answer · 2016-01-21T23:23:16

[Answer replaced: I wrote an answer yesterday but I think this one is better.]

VBA sees "copy" and "paste" operations as unrelated to each other. It also executes asynchronously - it starts executing an instruction, and then it may move on to executing the next one while the previous instruction is still being executed. It shouldn't do so if the second instruction depends on the first, but "paste" just takes ANYTHING from the clipboard, it is not aware of how things got on the clipboard. In particular, it doesn't relate specifically to the "copy" command right before it. Then, copying to the clipboard is a slow process; program execution gets to "paste" before anything is on the clipboard (or only partial, incomplete data is on it).

There are two ways to address the problem. One is to add a Sleep xxx instruction before the paste command. xxx is a number of milliseconds - and for Sleep to work, you need to declare it properly at the top of your module (it is a Windows function, not a VBA function). Google "VBA Sleep function" or something similar. With this solution, you have to experiment with xxx, sometimes 2 ms will suffice and sometimes 50 ms will not be enough. Not a good solution.

The better solution in your case would be to declare a Shape variable, assign to it the shape you were copying, and then add it to your target slide. This should work, because the shape (saved as the Shape variable) cannot be added to the new slide until the variable assignment completes. ASSUMING, of course, that VBA has a method for adding a shape to a slide...

Error in Code to Paste / Copy Shapes from Excel to PowerPoint VBA

1 Answers