Copy text to clipboard with Excel 365 VBA

Question

I want to copy text to the Windows 10 clipboard ready for pasting with Ctrl+V into an appropriate external application.

I found a promising answer I cannot get working: Copy Variable's Contents to Clipboard VBA (Excel 2013)

My simplified test code:

Sub TestCopyTextToVar()

Dim myData As DataObject
Dim Output As String
Output = "abc"

Set myData = New DataObject
myData.SetText Output
myData.PutInClipboard
MsgBox (Output & " Text has been copied")

End Sub

MsgBox displays Output correctly as 'abc'.

A paste into Notepad (or other apps, such as the intended application) displays nothing.

TextPad displays two question marks '??'.

This is apparently a know bug with SetText. Method. See here — JvdV

Hendy Sutarto Hendy Sutarto · Accepted Answer · 2021-03-07T09:38:06

Have you tried something like this?

Point your Excel cursor to non-empty cell, run the VBA script below, and then go to Notepad to paste the clipboard content.

Sub TestLoadActivecellToClipboard()
    Clipboard ActiveCell.Value
End Sub

' Copy Excel value to the Windows Clipboard
Function Clipboard$(Optional s$)
    Dim v: v = s  'Cast to variant for 64-bit VBA support
    With CreateObject("htmlfile")
    With .parentWindow.clipboardData
        Select Case True
            Case Len(s): .setData "text", v
            Case Else:   Clipboard = .GetData("text")
        End Select
    End With
    End With
End Function

Copy text to clipboard with Excel 365 VBA

1 Answers