vba 在代码执行期间保留剪贴板内容
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13327286/
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
Retaining clipboard content during code execution
提问by MisterWeary
This seems like it should be a simple problem but I've been unable to solve it. I have a program I'm writing where the user is babied through these steps:
这似乎应该是一个简单的问题，但我一直无法解决。我有一个我正在编写的程序，用户可以通过以下步骤进行操作：
Step 1:Go to another spreadsheet and copy the content
Step 2:Come back to my spreadsheet and press a button to paste that content
第 1 步：转到另一个电子表格并复制内容
第 2 步：返回到我的电子表格并按一个按钮粘贴该内容
When they press my button it needs to unlock the current sheet and paste the data without any of the formatting. This is what I have:
当他们按下我的按钮时，它需要解锁当前工作表并粘贴没有任何格式的数据。这就是我所拥有的：
ActiveWorkbook.ActiveSheet.Unprotect
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
So it works perfectly if I don't do the "Unprotect" line, but I'm trying to stop people from pasting the data without using my button.
因此，如果我不执行“取消保护”行，它可以完美运行，但是我试图阻止人们在不使用我的按钮的情况下粘贴数据。
I know that the issue is something to do with clipboard content being lost when I unprotect the sheet but the methods I've used to retain that content aren't working, namely:
我知道这个问题与我取消保护工作表时剪贴板内容丢失有关，但我用来保留该内容的方法不起作用，即：
- Lib User32 - OpenClipboard and CloseClipboard
- DataObject - GetFromClipboard, PutInClipboard
- Lib User32 - OpenClipboard 和 CloseClipboard
- DataObject - GetFromClipboard、PutInClipboard
Curiously, it works perfectly if I debug the VBA and step through line by line?!?
奇怪的是，如果我调试 VBA 并逐行执行，它会完美运行？！？
回答by MisterWeary
Okay, I've solved it (but someone may have a better solution). I believe the problem was that when I used the DataObject method of keeping the clipboard contents it stripped the formatting and so it couldn't PasteSpecial, that's actually fine with me because I'm only after the data anyway! With that in mind, this seems to work:
好的，我已经解决了（但有人可能有更好的解决方案）。我相信问题在于，当我使用 DataObject 方法来保留剪贴板内容时，它剥离了格式，因此它不能 PasteSpecial，这对我来说实际上很好，因为无论如何我只是在处理数据！考虑到这一点，这似乎有效：
Dim dClipBoard As MsForms.DataObject
Dim sClipBoard As String
Set dClipBoard = New MsForms.DataObject
dClipBoard.GetFromClipboard
sClipBoard = dClipBoard.GetText
ActiveWorkbook.ActiveSheet.Unprotect
Set dClipBoard = New MsForms.DataObject
dClipBoard.SetText sClipBoard
dClipBoard.PutInClipboard
Range("A1").Select
ActiveSheet.Paste
回答by Daniel
This doesn't directly address your issue, but... why not avoid copy and paste?
这并不能直接解决您的问题，但是...为什么不避免复制和粘贴？
If you are only pasting the values, then you don't need to copy. When you do step 1. "Go to another spreadsheet and copy the content" instead of copying, you can store the values into an array. When you want to "paste" the values, just place the array back into the ActiveSheet.
如果您只是粘贴值，则无需复制。当您执行第 1 步“转到另一个电子表格并复制内容”而不是复制时，您可以将值存储到数组中。当您想“粘贴”值时，只需将数组放回ActiveSheet.
Now that I've said that, here's example code:
说了这么多，下面是示例代码：
Private myArray
Sub fakecopy()
myArray = Selection
End Sub
Sub FakePaste()
With ActiveSheet
If IsArray(myArray) Then
.Range(.Cells(1, 1), .Cells(UBound(myArray), UBound(myArray, 2))) = myArray
Else
.Cells(1, 1) = myArray
End If
End With
End Sub
When you want to paste the values, just set the appropriate range to equal the array.
当您要粘贴值时，只需将适当的范围设置为等于数组即可。
Additionally, if when you protect the sheet you set UserInterfaceOnly = true, then there is no need to unprotect it to run your code.
此外，如果您在保护工作表时设置了UserInterfaceOnly = true，则无需取消保护即可运行您的代码。
