尝试将图表、范围等插入到 Word 中时，在 excel 2010 VBA 中复制/粘贴时出错
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12077171/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Error with copy/paste in excel 2010 VBA when trying to insert charts, ranges etc into word
提问by Gravity Kills
In researching this error I've come to the conclusion that it has to do with the clipboard not clearing like it should which wasn't an issue when we were using 2003 but is now that we are using 2010. (I also inherited this code from someone who doesn't work here anymore)
在研究这个错误时，我得出的结论是，它与剪贴板没有像它应该的那样清除有关，这在我们使用 2003 时不是问题，但现在我们使用的是 2010。（我也继承了这个代码来自不再在这里工作的人）
Run-Time error "4605":
This method or property is not available because the clipboard is empty or not valid.
运行时错误“4605”：
此方法或属性不可用，因为剪贴板为空或无效。
This is my code:
这是我的代码：
Worksheets("Exec Sum").Range("B140:I186").Copy
With myDoc.Bookmarks
.Item("b3").Range.PasteSpecial Link:=True, DataType:=wdPasteMetafilePicture, Placement:=bmark, DisplayAsIcon:=False
End With
' Clears Clipboard
Application.CutCopyMode = False
I've tried sendkeys ("^C") with selecting an empty cell first but no luck. Is there any way that I could either not use the copy/paste method to do this or a different way to clear the clipboard?
我已经尝试过先选择一个空单元格的 sendkeys ("^C") 但没有运气。有什么办法可以不使用复制/粘贴方法来执行此操作或使用不同的方法来清除剪贴板？
回答by scott
I started getting this error when I upgraded from 2003 to 2010, but the macro still worked in 2003.
我从 2003 年升级到 2010 年时开始收到此错误，但该宏在 2003 年仍然有效。
The tables were there, but I noticed that sometimes the copying didn't work. As this didn't ever happen in debug mode, I added 5 secs waiting time right before the copying.
桌子在那里，但我注意到有时复制不起作用。由于这在调试模式下从未发生过，因此我在复制之前添加了 5 秒的等待时间。
This makes the macro slower, but at least it works.
这会使宏变慢，但至少它可以工作。
回答by Steven
I'm still testing my solution but it's different than the suggestions made and so far has worked flawlessly. Just a different approach:
我仍在测试我的解决方案，但它与提出的建议不同，并且到目前为止工作得很好。只是一种不同的方法：
I add a label and surround my attempt to copy with an error handler...
我添加了一个标签并用错误处理程序包围了我的复制尝试...
Pg1CopyAttempt:
DoEvents
shSomeSheet.Range("A1:G30").Copy
On Error GoTo Pg1PasteFail
WordApp.Selection.PasteExcelTable False, False, False
On Error goto 0 'disable the error handler
I call the label "Pg1CopyAttempt" because I know that Excel, through no fault of my own, may fail to copy it to the clipboard. If Excel does let me down, I won't know until I try to paste. When I do try I get thrown to the error Handler (Pg1PasteFail) when the paste method fails. This sits at the bottom of my routine (after a straight exit sub or an exit routine).
我将标签称为“Pg1CopyAttempt”，因为我知道 Excel 可能无法将其复制到剪贴板，这并非我自己的过错。如果 Excel 确实让我失望了，我不知道，直到我尝试粘贴。当我尝试时，当粘贴方法失败时，我会被抛出错误处理程序 (Pg1PasteFail)。这位于我的例程的底部（在直接退出子程序或退出例程之后）。
Pg1PasteFail:
If Err.Number = 4605 Then ' clipboard is empty or not valid.
DoEvents
Resume Pg1CopyAttempt
End If
It would be better to test for an empty clipboard programatically than to rely on an error handler but the idea here is to force a loop so that it keeps trying to copy until it succeeds. The simpler loop with programatic testing of the clipboard could exploit Sean's ClipboardEmpty Function (above). DoEvents is still exploited but even with DoEvents the routines can fail and are just instructed to keep trying.
以编程方式测试空剪贴板会比依赖错误处理程序更好，但这里的想法是强制循环，以便它不断尝试复制直到成功。对剪贴板进行编程测试的更简单的循环可以利用 Sean 的 ClipboardEmpty 函数（上图）。DoEvents 仍然被利用，但即使使用 DoEvents，例程也可能失败，只是被指示继续尝试。
回答by SeanC
This is the code I use:
这是我使用的代码：
Private Declare Function apiOpenClipboard Lib "user32" Alias "OpenClipboard" (ByVal hWnd As Long) As Long
Private Declare Function apiEmptyClipboard Lib "user32" Alias "EmptyClipboard" () As Long
Private Declare Function apiCloseClipboard Lib "user32" Alias "CloseClipboard" () As Long
Private Declare Function CountClipboardFormats Lib "user32" () As Long
Function ClipboardEmpty() As Boolean
ClipboardEmpty = (CountClipboardFormats() = 0)
End Function
Sub EmptyClipboard()
If apiOpenClipboard(0&) <> 0 Then
Call apiEmptyClipboard
Call apiCloseClipboard
End If
End Sub
the function ClipboardEmptyis a test. e.g. if clipboardempty then
The sub EmptyClipboardwill simply clear the clipboard
该功能ClipboardEmpty是一个测试。例如if clipboardempty then
subEmptyClipboard将简单地清除剪贴板
回答by Everett S
I haven't confirmed this as a final solution, but the hang up seems to be when copying - The copied data never makes it onto the clipboard, so the code fails when attempting to paste. Clearing the clipboard of any previously copied data before initiating the next copy command seemed to help. I still am using the wait time shown above, but I seem to be able to get away with a shorter wait time using the following function to clear the clipboard:
我尚未确认这是最终解决方案，但似乎在复制时挂断了 - 复制的数据从未将其放入剪贴板，因此尝试粘贴时代码失败。在启动下一个复制命令之前清除任何先前复制数据的剪贴板似乎有帮助。我仍在使用上面显示的等待时间，但我似乎能够使用以下功能清除剪贴板以缩短等待时间：
Application.CutCopyMode = False
Application.CutCopyMode = False
