Excel 2013 64-bit VBA: Clipboard API doesn't work

Question

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?

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.

(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.)

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.

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

Does the `SetClipboardData()` call succeed? If not, what does `GetLastError()` report? — Jonathan Potter, Sep 07 '13 at 02:07
Just tried it. Clipboard_SetData("fjdkla;jfd") \ Debug output: \ hGlobalMemory is 287253201176 \ lpGlobalMemory is 287450358016 \ lpGlobalMemory is 287362598488 \ hClipMemory is 287253201176 \ LastDLLError is 0 \ I wonder why lstrcopy returns a different address than GlobalLock. I investigated the [lstrcopy API page](http://msdn.microsoft.com/en-us/library/windows/desktop/ms647490(v=vs.85).aspx) and Microsoft is warning us not to use it. I wonder if it's being disabled by some kind of Windows 8 security feature. Anyone know how to use [StringCchCopy](http://bit.ly/15N1jBR) in VBA? — Baodad, Sep 08 '13 at 04:05
The mentioned file 'win32api_ptrsafe.txt' can now be downloaded from 'Office 2010 Help Files: Win32API_PtrSafe with 64-bit Support' (http://www.microsoft.com/en-us/download/details.aspx?id=9970) — Andreas J, Mar 07 '14 at 15:48
As of 12/12/2021, this code works unmodified in Microsoft® Excel® for Microsoft 365 MSO (Version 2111 Build 16.0.14701.20206) 64-bit. — mbmast, Dec 12 '21 at 22:05

score 20 · Accepted Answer · edited Sep 24 '13 at 08:54

20

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.

Works for me on windows 7. Hope it works for you!

edited Sep 24 '13 at 08:54

answered Sep 24 '13 at 03:50

Jason Kurtz

266
1
3

Thanks, this worked: and I note that you're using a pointer as the return type, not a long integer - there's code on other sites using Long or LongLong, which will work just fine until it doesn't. – Nigel Heffernan May 04 '16 at 14:41

score 14 · Answer 2 · answered Feb 19 '16 at 18:16

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

 '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

your code works! However, i see that though there is some clipboard text present, EmptyClipboard() does not seem to clear the clipboard at all! Any suggestions as to why this is not working on Excel 2016/2019 64 bit versions? — sifar, Aug 21 '19 at 13:42
for e.g. Sub ClearClipboard() Dim x As Long Public Const APP_TITLE As String = "API Clipboard Clear" On Error GoTo ErrorHandler_ OpenClipboard (0&) EmptyClipboard CloseClipboard Exit Sub ErrorHandler_: MsgBox "Error: " & Err.Description, vbCritical, APP_TITLE End Sub — sifar, Aug 21 '19 at 13:49
This just worked for me on the same error in Access 2016. You rock! — T-Heron, May 19 '22 at 01:09

iDevlop · Answer 3 · 2023-02-10T05:40:43.163

I know that this question is now closed, but I prefer this much simpler approach, which will work independently of the architecture. And I like the approach of a single function to either read/write the clipboard.

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

score 0 · Answer 4 · answered Sep 23 '13 at 06:12

Use the code exactly as shown here:

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

except insert PtrSafe after Declare for all the API declarations.

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

This code does not work in 64-bit Excel 2013. The kernel32 API declarations aren't LongPtr. It errors in GlobalUnlock. My code in the main body of the question does not error and the APIs are declared for 64-bit. But thanks for trying. — Baodad, Sep 23 '13 at 15:04

Excel 2013 64-bit VBA: Clipboard API doesn't work

4 Answers4

Linked