vba 粘贴前检查剪贴板的内容
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7363907/
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
Check content of clipboard before pasting
提问by Aziz
Is it possible to check content of clipboard before pasting it in Excel VBA
是否可以在将剪贴板粘贴到 Excel VBA 之前检查剪贴板的内容
I have this today:
我今天有这个：
Sheets.Add After:=Sheets(Sheets.Count) ' Create new sheet
ActiveSheet.Paste ' Paste from Clipboard
IsMultiLevel = (InStr(Range("A1"), "Multi-Level") > 0) ' Determine type of report
If Not IsMultiLevel Then
MsgBox ("ERROR in Clipboard Data!!")
End
Else
ActiveSheet.Delete
End If
Bu I whish to check data before adding new sheet, then I dont need to delete it.. I want somthing like this
但是我希望在添加新工作表之前检查数据，然后我不需要删除它..我想要这样的东西
IsMultiLevel = (InStr([CLIPBOARD], "Multi-Level") > 0) ' Determine type of report
If Not IsMultiLevel Then
MsgBox ("ERROR in Clipboard Data!!")
End
End If
Sheets.Add After:=Sheets(Sheets.Count) ' Create new sheet
ActiveSheet.Paste ' Paste from Clipboard
采纳答案by MikeD
Coming from Excel 2003 I must say it is possible to examine the clipboard content by making use of the MSForms.DataObject. You first have to create a reference (VBA window tools / reference) to the Microsoft Forms 2.0 Object library (usually found at ...\system32\FM20.DLL).
来自 Excel 2003 我必须说可以通过使用MSForms.DataObject. 您首先必须创建对 Microsoft Forms 2.0 对象库（通常位于 ...\system32\FM20.DLL）的引用（VBA 窗口工具/引用）。
Then you can read the clipboard into a text variable:
然后您可以将剪贴板读入文本变量：
Dim BufObj As MSForms.DataObject, BufTxt as String
Set BufObj = New MSForms.DataObject
BufObj.GetFromClipboard
BufTxt = Buf.GetText
The buffer text will remain untouched (at least in Win XP/SP3, MS Office 2003 SP 3) and available for further use, i.e. the GetFromClipboardwon't destroy the clipboard buffer. The thing to consider here is that the clipboard content is available "as text" so any graphic will be stored in a raw text mode. Also the buffer size needs to be considered, as a variable length string in Excel can hold not more than ca. 2^31 characters (but IMHO this should be enough for 90% of all needs).
缓冲区文本将保持不变（至少在 Win XP/SP3、MS Office 2003 SP 3 中）并可供进一步使用，即GetFromClipboard不会破坏剪贴板缓冲区。这里要考虑的事情是剪贴板内容可以“作为文本”使用，因此任何图形都将以原始文本模式存储。还需要考虑缓冲区大小，因为 Excel 中的可变长度字符串不能超过 ca。2^31 个字符（但恕我直言，这应该足以满足 90% 的需求）。
回答by Aziz
Thanks a lot Guys..! You are amazing! I just wanted to share my solution with you.
非常感谢伙计们..！你太棒了！我只是想和你分享我的解决方案。
Function GetClipboardText(nChars As Integer) As String
Dim BufObj As MSForms.DataObject
Set BufObj = New MSForms.DataObject
BufObj.GetFromClipboard
GetClipboardText = Left(BufObj.GetText, nChars) ' Get only first nChars
End Function
Sub CreateOverviewSheet()
' Determine type of report in Clipboard
IsMultiLevel = (InStr(GetClipboardText(100), "Multi-Level") > 0)
IsConsolidated = (InStr(GetClipboardText(100), "Consolidated") > 0)
If Not IsMultiLevel Or IsConsolidated Then
MsgBox ("ERROR in Clipboard Data!!")
End
End If
Sheets.Add After:=Sheets(Sheets.Count) ' Create new sheet
ActiveSheet.Paste ' Paste from Clipboard
.
. and so on...
.
End Sub
回答by Chris Thornton
Please understand that there is no way to look at the clipboard contents (aside from examining the list of available formats) without disturbing other apps. If the app that placed the data on the clipboard is using Delayed Rendering, you'll trigger an event that cannot be undone (the app will be forced to produce the data, and will expect that the user has pasted the data). This will also have implications for clipboard synchronization across network connections, such as with Remote Desktop. And any of my users (ClipMate) will be quite annoyed with you, and will probably add you to the "wall of shame" of apps that destructively pre-view clipboard data: http://www.thornsoft.com/faq/index.php?action=artikel&cat=9&id=79
请理解，在不干扰其他应用程序的情况下，无法查看剪贴板内容（除了检查可用格式列表）。如果将数据放置在剪贴板上的应用程序正在使用延迟渲染，您将触发一个无法撤消的事件（应用程序将被迫生成数据，并期望用户已粘贴数据）。这也会对跨网络连接的剪贴板同步产生影响，例如使用远程桌面。我的任何用户（ClipMate）都会对你很生气，并且可能会将你添加到破坏性预览剪贴板数据的应用程序的“耻辱之墙”：http: //www.thornsoft.com/faq/index .php?action=artikel&cat=9&id=79
