VBA Post to Clipboard

[David J]

I'm using a method widely promoted on the web to copy to the clipboard to post a string to the clipboard generated from data in the active worksheet so that I can paste it into another application. I've activated MicrosoftForms 2.0 ObjectLibrary in Tools/References to no effect even after restarting Excel.

This is the essence of my code in Excel 2019 under Win10 Pro. Running it just generates two ASCII 63 characters on the clipboard. Feeding text from a string variable generates the same result. Inspecting the object reveals no content.

Sub clipboardTest()

Dim MSForms_DataObject As Object
Set MSForms_DataObject = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")

MSForms_DataObject.SetText "Just testing"
MSForms_DataObject.PutInClipboard
Set MSForms_DataObject = Nothing

End Sub

Where am I going wrong and what should I do about it?

BTW, ideally I'd like to store the macro in my Personal Macro workbook rather than in individual workbooks if that makes any difference.

Thanks for any help.

 

[RoryA]

This is a fairly well-known bug in Windows 8 and later versions that occurs if you have any Explorer windows open. It is safer to use Windows API calls instead of the data object, or use something like these functions:

Function SetClipBoardText(ByVal Text As Variant) As Boolean
    SetClipBoardText = CreateObject("htmlfile").ParentWindow.ClipboardData.SetData("Text", Text)
End Function
Function GetClipBoardText() As String
    On Error Resume Next
    GetClipBoardText = CreateObject("htmlfile").ParentWindow.ClipboardData.getData("Text")
End Function
Function ClearClipBoardText() As Boolean
    ClearClipBoardText = CreateObject("htmlfile").ParentWindow.ClipboardData.clearData("Text")
End Function

 

[David J]

This is a fairly well-known bug in Windows 8 and later versions that occurs if you have any Explorer windows open. It is safer to use Windows API calls instead of the data object, or use something like these functions:

Function SetClipBoardText(ByVal Text As Variant) As Boolean
    SetClipBoardText = CreateObject("htmlfile").ParentWindow.ClipboardData.SetData("Text", Text)
End Function
Function GetClipBoardText() As String
    On Error Resume Next
    GetClipBoardText = CreateObject("htmlfile").ParentWindow.ClipboardData.getData("Text")
End Function
Function ClearClipBoardText() As Boolean
    ClearClipBoardText = CreateObject("htmlfile").ParentWindow.ClipboardData.clearData("Text")
End Function

Many thanks. Works a treat. Frustrating that I couldn't find anything from MS on this.

 

[RoryA]

I seem to recall seeing it mentioned in a kb article somewhere, but don't have the link. It's been mentioned numerous times on MSDN and Answers since about 2012 and they still haven't fixed it...

 

[David J]

Good memory!

I am only an occasional VBA author and rather hoped Google would find something helpful in response to 'Excel VBA clipboard' in this case.

Lots of examples of the code I first quoted, and some very complex examples from MS, but I didn't see any mention of your solution, or of the Explorer conflict which explains why my macro worked fine at first and then sulked.

Thanks again.

 

[richardlp]

Hello, I am having all kinds of issues with copying to clipboard. When I use the code RoryA provided I get message, "Run-time error '429' ActiveX component can't create object. I am not sure if I am suppose to be defining anything with regard to this code. Also, I am using a company computer, so I think I am fighting some security settings. Please help.

 

[David J]

This is what I am using to take an Excel 2019 range (vertical or horizontal single row/column) containing email addresses to put a delimited list onto the clipboard that I can then paste into an Outlook email address field. Works perfectly under Win10 (fully updated) here.

Option Explicit

Sub emailList()

'***********************************************************
' concatenate list of email addresses for use in bulk email
'
' select cells containing required addresses and run macro
'
' 13Aug20
'***********************************************************

Dim myList As String
Dim email As Range
Dim separator As String

myList = ""
separator = "; "

Dim test As String

test = "just testing"

For Each email In Application.Selection.Cells
myList = myList & email & separator
Next email

SetClipBoardText (myList)

'Notify User
MsgBox "Email list in clipboard ready to paste into email", vbInformation

End Sub

'***************************************
'
' from RoryA via Mr Excel forum 13Aug20
'
'***************************************


Function SetClipBoardText(ByVal Text As Variant) As Boolean
SetClipBoardText = CreateObject("htmlfile").ParentWindow.ClipboardData.SetData("Text", Text)
End Function

 

[richardlp]

Thank you, I learned something. This works as long as I do not save the workbook as .xlsm. That is when I start to get the error message.

 

[David J]

That's not how it works for me. I'm successfully using a .xlsm file.