Having stored data to the clipboard using, .SetText, when I try to paste the text elsewhere, it is blank

Question

I am displaying a listbox from which the user may select lines. The code then reads from the spreadsheet cells associated with that list item and set a number of text variables. These are then concatenated and stored in to an array.

Finally, the array is read and the strings copied to the Clipboard.

However, when I try to use CTRL-v to paste the values in to an email/text file, no text is entered (the Clipboard appears to be empty).

Below is the salient code...

NB. Code based on a 3-line selection from the Listbox

Dim LinesOfData() As String

'Other code that doesn't have direct relevance.. builds the strings

Set MyData = New DataObject

MyData.Clear

Dim record As Variant
Dim Indentifier As String

j = 0

For Each record In LinesOfData
    Indentifier = "FormatID" & CStr(j)
    Debug.Print record
    MyData.SetText record, Indentifier
    MyData.PutInClipboard
    MyData.GetFromClipboard
    '
    ' Test code only
    '
    If j = 0 Then                            'First pass, only 1 record to check
        S = MyData.GetText("FormatID0")
        Debug.Print S                        'Shows correct value
    ElseIf j = 1 Then                        '2nd pass, 2 records to check
        S = MyData.GetText("FormatID0")
        Debug.Print S                        'Shows correct value
        S = MyData.GetText("FormatID1")
        Debug.Print S                        'Shows correct value
    ElseIf j = 2 Then
        S = MyData.GetText("FormatID0")
        Debug.Print S                        'Shows correct value
        S = MyData.GetText("FormatID1")
        Debug.Print S                        'Shows correct value
        S = MyData.GetText("FormatID2")
        Debug.Print S                        'Shows correct value
    End If
    ' If I go and try to paste in another appl'n at this point, nothing!
    j = j + 1
Next record

' End code

Now it doesn't matter if I exit the particular userform, or the application, I still have an empty Clipboard (despite being able to read the data from the Clipboard within the code).

Sheepdog Sheepdog · Accepted Answer · 2019-02-24T00:40:20

As you might expect, I kept looking for an asnwer and indeed I found it.

THIS IS A WINDOWS BUG!

Apparently, this occurs when you have 2 or more Explorer windows open (since Windows 8) and I had just that the entire time I was testing. Once I had only the one window open, viola! it worked.

Not sure if this is allowed. but here is the thread that alerted me, with code to achieve the result using API calls, provided by, Debaser at the end of the post.

https://chandoo.org/forum/threads/clipboard-copy-vba-code-not-working-in-windows-10.37126/

Hopefully, others will find this useful.

Having stored data to the clipboard using, .SetText, when I try to paste the text elsewhere, it is blank

1 Answers