Excel VBA 宏：在粘贴之前检查内容（剪贴板的？）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1108947/
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
Excel VBA Macro: Check content (of clipboard?) before pasting
提问by erikric
I've had some serious issues with pasting data from various sources into Excel. Excel tends to try to be smart and does all kinds of silly formating. We need the data as text.
我在将各种来源的数据粘贴到 Excel 时遇到了一些严重的问题。Excel 趋向于尝试变得聪明并进行各种愚蠢的格式化。我们需要将数据作为文本。
The problem is that we have a lot of users, and many of them are not very experienced with computers, so asking them to use right-click and 'Paste Special' every time is not an option.
问题是我们有很多用户，而且他们中的许多人对计算机的经验不是很丰富，因此要求他们每次都使用右键单击和“选择性粘贴”并不是一种选择。
I found a solution in recording a macro that uses 'Paste Special' and 'text', and overriding the ctrl-v to use this function. It seemed to work perfectly, until I marked a cell, copied it, and tried to paste it. The macro crashed.
我在录制使用“选择性粘贴”和“文本”的宏并覆盖 ctrl-v 以使用此功能时找到了解决方案。它似乎工作得很好，直到我标记了一个单元格，复制它，并试图粘贴它。宏崩溃了。
So what I need is a function that can check if I am trying to paste some copied text, and then use this line:
所以我需要的是一个函数，它可以检查我是否试图粘贴一些复制的文本，然后使用这一行：
ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _
False
While if I am pasting a marked cell, I want to run this line (to paste just the value):
如果我粘贴一个标记的单元格，我想运行这一行（只粘贴值）：
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
I am not very experienced in writing VBA macros for Excel (and I hope I never have to be), so if anyone have a few pointers, I'd be most grateful.
我在为 Excel 编写 VBA 宏方面不是很有经验（我希望我永远不必如此），所以如果有人有一些指点，我将不胜感激。
回答by Gary McGill
For clipboard access/manipulation, you'll want to add a reference to the Microsoft Forms 2.0 library in Project->References. You can then use the MSForms.DataObjectclass that has (among others) a GetFormatmethod to check whether the clipboard has a particular type of data.
对于剪贴板访问/操作，您需要在 Project->References 中添加对 Microsoft Forms 2.0 库的引用。然后，您可以使用MSForms.DataObject具有（除其他外）GetFormat方法的类来检查剪贴板是否具有特定类型的数据。
Thisis a pretty good intro to clipboard handling using DataObject.
这是使用DataObject.
回答by Flephal
Sub PasteAsText() ' Assign Keyboard Shortcut: Ctrl+v
Application.ScreenUpdating = False
Select Case Application.CutCopyMode
Case Is = False
On Error Resume Next
ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False
Case Is = xlCopy
If Not Range(GetClipboardRange).HasFormula Then
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Else
ActiveSheet.Paste
End If
Case Is = xlCut
ActiveSheet.Paste
End Select
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
Function GetClipboardRange() As String
' Edited from http://www.ozgrid.com/forum/showthread.php?t=66773
Dim formats 'Check to make sure clipboard contains table data
formats = Application.ClipboardFormats
For Each fmt In formats
If fmt = xlClipboardFormatCSV Then
Application.ActiveSheet.Paste Link:=True 'Paste link
Dim addr1, addr2 As String 'Parse formulas from selection
addr1 = Application.Substitute(Selection.Cells(1, 1).Formula, "=", "")
addr2 = Application.Substitute(Selection.Cells(Selection.Rows.Count, Selection.Columns.Count).Formula, "=", "")
GetClipboardRange = addr1 & IIf(addr1 <> addr2, ":" & addr2, "")
Exit For
End If
Next
End Function
回答by Joel Goodwin
Have you considered making the cells in the target sheet equal to Text? When they're General, Excel does it's best-guess at what you expect to see.
您是否考虑过使目标工作表中的单元格等于文本？当它们是 General 时，Excel 会根据您期望看到的内容进行最佳猜测。
On the other hand if you really want to implement Paste Special...
另一方面，如果你真的想实现 Paste Special ...
There is no "Paste" event you can catch - you have catch every place that a paste could occur.
没有您可以捕捉到的“粘贴”事件 - 您可以捕捉到每个可能发生粘贴的地方。
For example, you can capture the CTRL-V keypress if you issue the following code when the workbook starts up (Workbook_Open):
例如，如果在工作簿启动 (Workbook_Open) 时发出以下代码，则可以捕获 CTRL-V 按键：
Application.OnKey "^v", "DoMyPaste"
This will call your function instead of the Excel paste function. Put something like this in a module:
这将调用您的函数而不是 Excel 粘贴函数。把这样的东西放在一个模块中：
Public Sub DoMyPaste()
If Selection.[is marked cell] Then
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Else
ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon _
:= False
End If
End Sub
I have not tested this, this is more of a rough sketch. Do note that Selection could be more than one cell, so your "check for a marked cell" needs to check the whole range in some way.
我没有测试过这个，这更像是一个粗略的草图。请注意，选择可能不止一个单元格，因此您的“检查标记的单元格”需要以某种方式检查整个范围。
This is just the tip of the iceberg though. If you want to a full solution, you should check out this article, which is the OCD version of catching all Paste calls:
不过这只是冰山一角。如果你想要一个完整的解决方案，你应该查看这篇文章，它是捕获所有 Paste 调用的 OCD 版本：
http://www.jkp-ads.com/Articles/CatchPaste.asp
回答by Andrew
This isn't the greatest solution, but it technically works. Just try them both.
这不是最好的解决方案，但它在技术上有效。试试他们两个。
On Error Resume Next
ActiveSheet.PasteSpecial Format:=Text, Link:=False, DisplayAsIcon:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
