logo标签列表

Excel 2013 64位 VBA：剪贴板API无法工作

vbawinapiexcel64-bitoffice-2013
24

以前我可以在Excel VBA中使用Windows API调用来设置剪贴板上的文本，但自从升级到64位Office 2013后，我就无法这样做了。下面是一些代码，它没有出错，但也没有在剪贴板上设置任何文本。是否有人可以帮助我测试和故障排除？

在将以下代码粘贴到VBA中的代码模块后，您可以在立即窗口中键入Clipboard_SetData("将此复制到剪贴板。")，它应该在剪贴板上设置该文本，并且您可以将其粘贴到任何其他应用程序中。

（我正在使用Windows 8，因此无法使用Microsoft Forms或数据对象来操作剪贴板。它在Windows 8上无法正常工作。）

更新和编辑：由于Jason Kurtz的答案，下面的代码已经得到纠正并且在64位Excel中正常工作。如果您觉得这很有用，请投票给他的答案。

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
2
SetClipboardData()调用成功吗？如果没有，GetLastError()报告了什么？ - Jonathan Potter
刚试了一下。Clipboard_SetData("fjdkla;jfd") \ Debug 输出：\ hGlobalMemory 是 287253201176 \ lpGlobalMemory 是 287450358016 \ lpGlobalMemory 是 287362598488 \ hClipMemory 是 287253201176 \ LastDLLError 是 0 \ 我想知道为什么 lstrcopy 返回的地址与 GlobalLock 不同。我调查了 lstrcopy API 页面，微软警告我们不要使用它。我想知道是否被某种 Windows 8 安全功能禁用了。有人知道如何在 VBA 中使用 StringCchCopy 吗？ - Baodad
3
提到的文件 'win32api_ptrsafe.txt' 现在可以从 'Office 2010 帮助文件：带有64位支持的 Win32API_PtrSafe' (http://www.microsoft.com/en-us/download/details.aspx?id=9970) 下载。 - Andreas J
截至2021年12月12日，此代码在Microsoft 365 MSO（版本2111 Build 16.0.14701.20206）64位的Microsoft® Excel®中未经修改即可正常运行。 - mbmast
4个回答
20

好的，我现在明白了...

您需要更改代码版本中的这一行：

Private Declare PtrSafe Function lstrcpy Lib "kernel32" (ByVal lpString1 As String, ByVal lpString2 As String) As LongPtr

变为这样：

Private Declare PtrSafe Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, ByVal lpString2 As Any) As LongPtr

如果按照您原先的代码逐步执行，会发现在调用lstrcopy时lpGlobalMemory的值会改变。当类型改为Any后，该值将保持不变。

我在Windows 7上测试通过，希望对您也有帮助！

谢谢，这个可行：我注意到你使用指针作为返回类型，而不是长整型 - 其他网站上的代码使用Long或LongLong也可以正常工作，直到它不能。 - Nigel Heffernan
14

发布完整代码供他人使用。已在 Excel 2007、2010、2013、2016 的 32 位版本以及 64 位版本的 Excel 2013 上进行了测试并可以正常工作，均在 Windows 10 上运行。

 'https://dev59.com/5WUq5IYBdhLWcg3wKtNL
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
你的代码可用！但是，我发现尽管存在一些剪贴板文本，EmptyClipboard（）似乎根本没有清除剪贴板！对于Excel 2016/2019 64位版本，有什么建议为什么这不起作用？ - sifar
子 清空剪贴板() 短整型 x 公共常量 APP_TITLE As String = "API 剪贴板清空" 在错误处理_处继续执行 打开剪贴板 (0&) 清空剪贴板 关闭剪贴板 退出子程序 错误处理_: MsgBox "错误: " & Err.Description, vbCritical, APP_TITLE 结束子程序 - sifar
这个方法在我使用Access 2016时解决了同样的错误。你太棒了！ - T-Heron
3

我知道这个问题现在已经关闭了，但是我更喜欢这种简单得多的方法，它可以独立于架构工作。而且我喜欢使用一个函数来读写剪贴板的方法。

Function Clipboard(Optional StoreText As String) As String
'PURPOSE: Read/Write to Clipboard
'Source: ExcelHero.com (Daniel Ferry)

Dim x As Variant
'Store as variant for 64-bit VBA support
  x = StoreText
'Create HTMLFile Object
  With CreateObject("htmlfile")
    With .parentWindow.clipboardData
      Select Case True
        Case Len(StoreText)
          'Write to the clipboard
            .setData "text", x
        Case Else
          'Read from the clipboard (no variable passed through)
            Clipboard = .GetData("text")
      End Select
    End With
  End With
End Function
0
请完全按照此处所示的代码使用：

http://msdn.microsoft.com/en-us/library/office/ff192913.aspx

对于所有API声明，都应该在Declare之后加上PtrSafe。

代码应该放在一个独立的模块中。

像这样：

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
这段代码在64位的Excel 2013中无法运行。kernel32 API声明不是LongPtr类型。在GlobalUnlock处出现错误。我在问题主体中的代码没有错误，并且API已经声明为64位。但还是感谢你的尝试。 - Baodad
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接