Office 2013 Excel .PutInClipboard is Different?

The Problem (Q-score 8, ranked #79th of 95 in the VBA Core archive)

The scenario as originally posted in 2013

I’ve used a routine for years to put a plain text string into the clipboard that I can paste into another program such as:

targetData.SetText "This is a plain text string"
targetData.PutInClipboard

When I use this in Excel Office 2013 the data isn’t in the clipboard and therefore I can’t paste it. This never happened in prior versions.

Under closer inspection I’ve found that the string does go to the clipboard but as “System String” but not as “Text” or “Unicode Text”.

BUT… about 10% of the time it acutally works as it should putting the string into the clipboard as “Text”.

Any ideas??

Why this Access DoCmd / Recordset path keeps breaking

The scenario uses DoCmd or OpenRecordset, both of which are notorious for bubbling silent failures when the source query has uncommitted changes. The question captures a common debugging dead-end in VBA Core.

The Verified Solution — niche answer (below median) (+4)

5-line VBA Core pattern (copy-ready)

user2140261’s comment is the correct solution:

How to: Send Information to the Clipboard

(The following is just copied from the above link)

If you need to copy the contents of the active control on a form or report to the Clipboard, you only need this code:

Private Sub cmdCopy_Click() 
   Me!txtNotes.SetFocus 
   DoCmd.RunCommand acCmdCopy 
End Sub

However, this the replacement you need for your old routine:

1. Create a module, name it “WinAPI” or something, put this code in it:

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

Public Const GHND = &H42
Public Const CF_TEXT = 1
Public Const MAXSIZE = 4096

2. In the module where your old routine is defined, replace your old routine with this code:

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

3. Then, call it like this:

' doesn't work on Windows 8: targetData.SetText "This is a plain text string"
'doesn't work on Windows 8: targetData.PutInClipboard
ClipBoard_SetData ("This is a plain text string")

Error-handling details to lift with the snippet

This answer wires error flow through MsgBox / Err.Description. Keep that intact: stripping it to “make it cleaner” removes the signal you’ll need when the macro fails silently on a user machine.

When to Use It — classic (2013–2016)

Ranked #79th in its category — specialized fit

This pattern sits in the 97% tail relative to the top answer. Reach for it when your scenario closely matches the question title; otherwise browse the VBA Core archive for a higher-consensus alternative.

What changed between 2013 and 2026

The answer is 13 years old. The VBA Core object model has been stable across Office 2013, 2016, 2019, 2021, 365, and 2024/2026 LTSC, so the pattern still compiles. Changes that might affect you: 64-bit API declarations (use PtrSafe), blocked macros in downloaded files (Mark-of-the-Web), and the shift toward Office Scripts for web-first workflows.