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:
我在VBA中创建了一个代码，用于将Excel中的表格中的形状复制到现有的PowerPoint文件中，这些形状将添加到现有的幻灯片中。在Excel中，我有一个表，指示向哪个幻灯片以及在哪个位置放置每个形状(我命名了所有形状，以便我可以按名称引用它们)。我的代码循环遍历此表以获得形状并将其粘贴到指定的幻灯片。这是我的代码:
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.
当我第一次尝试的时候，效果很好。我制作了一个样本PPT，一切都很好。一旦我更改了使用官方PPT的路径(它的权重大约为120MB)，它就会显示以下错误:View。PasteSpecial:无效的请求。剪贴板是空的，或者包含可能没有粘贴到这里的数据。
Any ideas why this happens?
你知道为什么会这样吗?
1 个解决方案
#1
1
[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).
VBA将“复制”和“粘贴”操作视为互不相关的操作。它也异步执行——它开始执行一条指令，然后它可能继续执行下一条指令，而前一条指令仍在执行。如果第二个指令依赖于第一个指令，它不应该这么做，但是“粘贴”只是从剪贴板中获取任何内容，它不知道剪贴板上的内容是如何得到的。特别是，它与前面的“copy”命令没有特别的关系。然后，复制到剪贴板是一个缓慢的过程;程序执行可以在剪贴板上的任何东西之前“粘贴”(或者只是部分的、不完整的数据)。
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.
有两种方法可以解决这个问题。一个是在粘贴命令之前添加一个Sleep xxx指令。xxx是一个毫秒数，为了保证睡眠，您需要在模块的顶部正确地声明它(它是一个Windows函数，而不是VBA函数)。谷歌"VBA睡眠功能"或类似的东西。有了这个解决方案，您必须对xxx进行实验，有时2 ms就足够了，有时50 ms就不够了。不是一个好的解决方案。
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...
在这种情况下，更好的解决方案是声明一个形状变量，将复制的形状分配给它，然后将其添加到目标幻灯片中。这应该是可行的，因为在变量分配完成之前，不能将形状(保存为形状变量)添加到新的幻灯片中。当然，假设VBA有向幻灯片添加形状的方法……
#1
1
[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).
VBA将“复制”和“粘贴”操作视为互不相关的操作。它也异步执行——它开始执行一条指令，然后它可能继续执行下一条指令，而前一条指令仍在执行。如果第二个指令依赖于第一个指令，它不应该这么做，但是“粘贴”只是从剪贴板中获取任何内容，它不知道剪贴板上的内容是如何得到的。特别是，它与前面的“copy”命令没有特别的关系。然后，复制到剪贴板是一个缓慢的过程;程序执行可以在剪贴板上的任何东西之前“粘贴”(或者只是部分的、不完整的数据)。
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.
有两种方法可以解决这个问题。一个是在粘贴命令之前添加一个Sleep xxx指令。xxx是一个毫秒数，为了保证睡眠，您需要在模块的顶部正确地声明它(它是一个Windows函数，而不是VBA函数)。谷歌"VBA睡眠功能"或类似的东西。有了这个解决方案，您必须对xxx进行实验，有时2 ms就足够了，有时50 ms就不够了。不是一个好的解决方案。
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...
在这种情况下，更好的解决方案是声明一个形状变量，将复制的形状分配给它，然后将其添加到目标幻灯片中。这应该是可行的，因为在变量分配完成之前，不能将形状(保存为形状变量)添加到新的幻灯片中。当然，假设VBA有向幻灯片添加形状的方法……