如何清除剪贴板的内容

Question

在Excel工作表Products中，在一个名为Products的表中，我有示例数据。

我想将最左边的三列复制到剪贴板，然后将其内容保存到文件中，读取该文件的第一行，将其复制到剪贴板并粘贴到另一张工作表的单元格结果。

Dim DataObj As DataObject
Set DataObj = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") 'or simply New DataObject
Dim strOut As String, strIn As String
With Sheets("Products").ListObjects("Products")
Union(.ListColumns(1).DataBodyRange, .ListColumns(2).DataBodyRange, .ListColumns(3).DataBodyRange).Copy 'data copied to Clipboard
End With
DataObj.GetFromClipboard
strOut = DataObj.GetText 'data copied from Clipboard to string variable
DataObj.SetText Text:=Empty
DataObj.PutInClipboard 'Clipboard clearing #1
Application.CutCopyMode = False 'Clipboard clearing #2
ClearClipboard 'Clipboard clearing #3
Open ThisWorkbook.Path & "products.csv" For Output As #1
Print #1, strOut;
Close #1 'data saved
Open ThisWorkbook.Path & "products.csv" For Input As #1
Line Input #1, strIn
Close #1 'data loaded
DataObj.SetText strIn
DataObj.PutInClipboard 'data copied from string variable to Clipboard
Sheets("Results").Range("A1").PasteSpecial Paste:=xlPasteValues 'data should be pasted to single row
Application.CutCopyMode = False

已知的ClearClipboard代码：

Public Declare PtrSafe Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As LongPtr
Public Declare PtrSafe Function EmptyClipboard Lib "user32" () As LongPtr
Public Declare PtrSafe Function CloseClipboard Lib "user32" () As LongPtr
Sub ClearClipboard()
OpenClipboard (0&)
EmptyClipboard
CloseClipboard
End Sub

在填充strOut变量后，我应用了三种剪贴板清除方法来确保它是空的。由于只将文件中的第一行加载到strIn变量中，并通过Paste或PasteSpecial方法将其粘贴到单元格中，因此我希望看到一行数据，同时还有清除剪贴板之前的所有行。

Answer 1

您没有清除office剪贴板(其中复制了这3行(

用于清除办公室剪贴板：

#If VBA7 Then
Private Declare PtrSafe Function AccessibleChildren Lib "oleacc" (ByVal paccContainer As Office.IAccessible, _
ByVal iChildStart As Long, ByVal cChildren As Long, _
ByRef rgvarChildren As Any, ByRef pcObtained As Long) As Long
Public Const myVBA7 As Long = 1
#Else
Private Declare Function AccessibleChildren Lib "oleacc" (ByVal paccContainer As Office.IAccessible, _
ByVal iChildStart As Long, ByVal cChildren As Long, _
ByRef rgvarChildren As Any, ByRef pcObtained As Long) As Long
Public Const myVBA7 As Long = 0
#End If
Public Sub EvRClearOfficeClipBoard()
Dim cmnB, IsVis As Boolean, j As Long, Arr As Variant
Arr = Array(4, 7, 2, 0)                      '4 and 2 for 32 bit, 7 and 0 for 64 bit
Set cmnB = Application.CommandBars("Office Clipboard")
IsVis = cmnB.Visible
If Not IsVis Then
cmnB.Visible = True
DoEvents
End If
For j = 1 To Arr(0 + myVBA7)
AccessibleChildren cmnB, Choose(j, 0, 3, 0, 3, 0, 3, 1), 1, cmnB, 1
Next

cmnB.accDoDefaultAction CLng(Arr(2 + myVBA7))
Application.CommandBars("Office Clipboard").Visible = IsVis
End Sub