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 a table that indicates which slide and in which position to paste 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.

score 2 · Accepted Answer · 2016-01-22 20:39:56Z

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related