Excel 2013 64 位 VBA：剪贴板 API 不起作用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18668928/
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 2013 64-bit VBA: Clipboard API doesn't work
提问by Baodad
I used to be able to use Windows API calls in Excel VBA to set text on the clipboard. But ever since upgrading to 64-bit Office 2013, I cannot. Below is some code that does not error, but it is also not setting any text on the clipboard. Can someone help me test and troubleshoot?
我曾经能够在 Excel VBA 中使用 Windows API 调用来设置剪贴板上的文本。但是自从升级到 64 位 Office 2013 后，我就不能了。下面是一些没有错误的代码，但它也没有在剪贴板上设置任何文本。有人可以帮我测试和排除故障吗？
After pasting the code below into a code module in VBA, you can test it in the immediate windows by typing Clipboard_SetData("Copy this to the clipboard.")and it should set that text on the clipboard and you would be able to paste it into any other application.
将下面的代码粘贴到 VBA 中的代码模块后，您可以通过键入在直接窗口中测试它Clipboard_SetData("Copy this to the clipboard.")，它应该在剪贴板上设置该文本，您将能够将其粘贴到任何其他应用程序中。
(I am using Windows 8, so I cannot use Microsoft Forms or the Data Object to manipulate the clipboard. It does not work properly on Windows 8.)
（我使用的是 Windows 8，所以我无法使用 Microsoft Forms 或数据对象来操作剪贴板。它在 Windows 8 上无法正常工作。）
UPDATE and EDIT:Code below has been corrected and now works properly in 64-bit Excel, thanks to Jason Kurtz' answer below. If you find this useful, please vote his answer up.
更新和编辑：下面的代码已更正，现在可以在 64 位 Excel 中正常工作，感谢 Jason Kurtz 在下面的回答。如果你觉得这有用，请投票给他的答案。
Option Explicit
'Found 64-bit API declarations here: http://spreadsheet1.com/uploads/3/0/6/6/3066620/win32api_ptrsafe.txt
Private Declare PtrSafe Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, ByVal dwBytes As LongPtr) As LongPtr
Private Declare PtrSafe Function GlobalFree Lib "kernel32" (ByVal hMem As LongPtr) As LongPtr
Private Declare PtrSafe Function GlobalLock Lib "kernel32" (ByVal hMem As LongPtr) As LongPtr
Private Declare PtrSafe Function GlobalSize Lib "kernel32" (ByVal hMem As LongPtr) As LongPtr
Private Declare PtrSafe Function GlobalUnlock Lib "kernel32" (ByVal hMem As LongPtr) As Long
Private Declare PtrSafe Function OpenClipboard Lib "user32" (ByVal hwnd As LongPtr) As Long
Private Declare PtrSafe Function CloseClipboard Lib "user32" () As Long
Private Declare PtrSafe Function EmptyClipboard Lib "user32" () As Long
Private Declare PtrSafe Function SetClipboardData Lib "user32" (ByVal wFormat As Long, ByVal hMem As LongPtr) As LongPtr
Private Declare PtrSafe Function GetClipboardData Lib "user32" (ByVal wFormat As Long) As LongPtr
Private Declare PtrSafe Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, ByVal lpString2 As Any) As LongPtr
Private Const GMEM_MOVEABLE = &H2
Private Const GMEM_ZEROINIT = &H40
Private Const GHND = (GMEM_MOVEABLE Or GMEM_ZEROINIT)
Public Const CF_TEXT = 1
Public Const MAXSIZE = 4096
Sub ClipBoard_SetData(MyString As String)
'32-bit code by Microsoft: http://msdn.microsoft.com/en-us/library/office/ff192913.aspx
Dim hGlobalMemory As LongPtr, lpGlobalMemory As LongPtr
Dim hClipMemory As LongPtr, X As Long
' Allocate moveable global memory.
hGlobalMemory = GlobalAlloc(GHND, Len(MyString) + 1)
' Lock the block to get a far pointer to this memory.
lpGlobalMemory = GlobalLock(hGlobalMemory)
' Copy the string to this global memory.
lpGlobalMemory = lstrcpy(lpGlobalMemory, MyString)
' Unlock the memory.
If GlobalUnlock(hGlobalMemory) <> 0 Then
MsgBox "Could not unlock memory location. Copy aborted."
'Debug.Print "GlobalFree returned: " & CStr(GlobalFree(hGlobalMemory))
GoTo OutOfHere
End If
' Open the Clipboard to copy data to.
If OpenClipboard(0&) = 0 Then
MsgBox "Could not open the Clipboard. Copy aborted."
Exit Sub
End If
' Clear the Clipboard.
X = EmptyClipboard()
' Copy the data to the Clipboard.
hClipMemory = SetClipboardData(CF_TEXT, hGlobalMemory)
OutOfHere:
If CloseClipboard() = 0 Then
MsgBox "Could not close Clipboard."
End If
End Sub
采纳答案by Jason Kurtz
OK, I got it now...
好的，我现在明白了......
You need to change this line in your version of the code:
您需要在您的代码版本中更改此行：
Private Declare PtrSafe Function lstrcpy Lib "kernel32" (ByVal lpString1 As String, ByVal lpString2 As String) As LongPtr
To this:
对此：
Private Declare PtrSafe Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, ByVal lpString2 As Any) As LongPtr
If you step through the code as you had it, you will see that the value of lpGlobalMemory changes when lstrcopy is called. When the types are changed to Any, the value stays the same.
如果您按原样单步执行代码，您将看到在调用 lstrcopy 时 lpGlobalMemory 的值发生了变化。当类型更改为 Any 时，值保持不变。
Works for me on windows 7. Hope it works for you!
在 Windows 7 上对我有用。希望它对你有用！
回答by michaelf
Posting complete code for others. Tested and working on 32 Bit Versions of Excel 2007, 2010, 2013, 2016 and 64 Bit Excel 2013 All running on Windows 10
为其他人发布完整的代码。已在 32 位版本的 Excel 2007、2010、2013、2016 和 64 位 Excel 2013 上进行测试和工作 全部在 Windows 10 上运行
'http://stackoverflow.com/questions/14738330/office-2013-excel-putinclipboard-is-different
Option Explicit
#If VBA7 Then
Declare PtrSafe Function GlobalUnlock Lib "kernel32" (ByVal hMem As LongPtr) As LongPtr
Declare PtrSafe Function GlobalLock Lib "kernel32" (ByVal hMem As LongPtr) As LongPtr
Declare PtrSafe Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, ByVal dwBytes As LongPtr) As LongPtr
Declare PtrSafe Function CloseClipboard Lib "User32" () As Long
Declare PtrSafe Function OpenClipboard Lib "User32" (ByVal hwnd As LongPtr) As LongPtr
Declare PtrSafe Function EmptyClipboard Lib "User32" () As Long
Declare PtrSafe Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, ByVal lpString2 As Any) As LongPtr
Declare PtrSafe Function SetClipboardData Lib "User32" (ByVal wFormat As Long, ByVal hMem As LongPtr) As LongPtr
#Else
Declare Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) As Long
Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) As Long
Declare Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, ByVal dwBytes As Long) As Long
Declare Function CloseClipboard Lib "User32" () As Long
Declare Function OpenClipboard Lib "User32" (ByVal hwnd As Long) As Long
Declare Function EmptyClipboard Lib "User32" () As Long
Declare Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, ByVal lpString2 As Any) As Long
Declare Function SetClipboardData Lib "User32" (ByVal wFormat As Long, ByVal hMem As Long) As Long
#End If
Public Const GHND = &H42
Public Const CF_TEXT = 1
Public Const MAXSIZE = 4096
Function ClipBoard_SetData(MyString As String)
#If VBA7 Then
Dim hGlobalMemory As LongPtr, lpGlobalMemory As LongPtr, hClipMemory As LongPtr
#Else
Dim hGlobalMemory As Long, lpGlobalMemory As Long, hClipMemory As Long
#End If
Dim x As Long
' Allocate moveable global memory.
'-------------------------------------------
hGlobalMemory = GlobalAlloc(GHND, Len(MyString) + 1)
' Lock the block to get a far pointer
' to this memory.
lpGlobalMemory = GlobalLock(hGlobalMemory)
' Copy the string to this global memory.
lpGlobalMemory = lstrcpy(lpGlobalMemory, MyString)
' Unlock the memory.
If GlobalUnlock(hGlobalMemory) <> 0 Then
MsgBox "Could not unlock memory location. Copy aborted. Please contact 14Fathoms."
GoTo OutOfHere2
End If
' Open the Clipboard to copy data to.
If OpenClipboard(0&) = 0 Then
MsgBox "Could not open the Clipboard. Copy aborted. Please contact 14Fathoms."
Exit Function
End If
' Clear the Clipboard.
x = EmptyClipboard()
' Copy the data to the Clipboard.
hClipMemory = SetClipboardData(CF_TEXT, hGlobalMemory)
OutOfHere2:
If CloseClipboard() = 0 Then
MsgBox "Could not close Clipboard. Please contact 14Fathoms."
End If
End Function
Sub TestCOPYPASTE()
Call ClipBoard_SetData("Hello World " & now())
'Open notepad or in the immediate window and hit control-v
End Sub
回答by Jason Kurtz
Use the code exactly as shown here:
完全按照此处所示使用代码：
http://msdn.microsoft.com/en-us/library/office/ff192913.aspx
http://msdn.microsoft.com/en-us/library/office/ff192913.aspx
except insert PtrSafe after Declare for all the API declarations.
除了在所有 API 声明的 Declare 之后插入 PtrSafe。
The code should be in a module by itself.
代码本身应该在一个模块中。
Like this:
像这样：
Option Explicit
Declare PtrSafe Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) _
As Long
Declare PtrSafe Function GlobalLock Lib "kernel32" (ByVal hMem As Long) _
As Long
Declare PtrSafe Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, _
ByVal dwBytes As Long) As Long
Declare PtrSafe Function CloseClipboard Lib "User32" () As Long
Declare PtrSafe Function OpenClipboard Lib "User32" (ByVal hwnd As Long) _
As Long
Declare PtrSafe Function EmptyClipboard Lib "User32" () As Long
Declare PtrSafe Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, _
ByVal lpString2 As Any) As Long
Declare PtrSafe Function SetClipboardData Lib "User32" (ByVal wFormat _
As Long, ByVal hMem As Long) As Long
Public Const GHND = &H42
Public Const CF_TEXT = 1
Public Const MAXSIZE = 4096
Function ClipBoard_SetData(MyString As String)
Dim hGlobalMemory As Long, lpGlobalMemory As Long
Dim hClipMemory As Long, X As Long
' Allocate moveable global memory.
'-------------------------------------------
hGlobalMemory = GlobalAlloc(GHND, Len(MyString) + 1)
' Lock the block to get a far pointer
' to this memory.
lpGlobalMemory = GlobalLock(hGlobalMemory)
' Copy the string to this global memory.
lpGlobalMemory = lstrcpy(lpGlobalMemory, MyString)
' Unlock the memory.
If GlobalUnlock(hGlobalMemory) <> 0 Then
MsgBox "Could not unlock memory location. Copy aborted."
GoTo OutOfHere2
End If
' Open the Clipboard to copy data to.
If OpenClipboard(0&) = 0 Then
MsgBox "Could not open the Clipboard. Copy aborted."
Exit Function
End If
' Clear the Clipboard.
X = EmptyClipboard()
' Copy the data to the Clipboard.
hClipMemory = SetClipboardData(CF_TEXT, hGlobalMemory)
OutOfHere2:
If CloseClipboard() = 0 Then
MsgBox "Could not close Clipboard."
End If
End Function
