Error: "OpenClipboard Failed"

[vaniificat]

Hello,

I've been using a certain subroutine to copy and paste the values of particular cells from Excel into a different program, and until recently this has been working fantastically. But now, all of a sudden, when I try to run this sub on certain cells, I get this error: "-2147221040 (800401D0) - OpenClipboard Failed". I call the copy-and-paste sub on a lot of different cells in various macros, but for some reason the error is always thrown on the third line:

Public Sub VeryFirstPartOfOneOfMySubs(
    CP ("$CB$3")  'This works fine
    Sleep (100)
    CP ("$BA$16") 'The error shows up here
    Sleep (50)
    ...
End Sub

I don't think it's an issue of increasing the sleep time, because it's never needed it in the past. Here's the derivative sub ("CP", short for "copy-paste"):

Private Sub CP(Rnge As String)
    Dim MyData As New MSForms.DataObject
    MyData.SetText Range(Rnge).Value
    MyData.PutInClipboard  'The error shows up here
    RC  'Short for "right-click"; simulates a right-click in another app to paste
    Sleep (50)
End Sub

The only thing I can think of is that something I changed in another macro somewhere could be conflicting with normal clipboard functioning - but I don't know why that would be. The main thing I changed was that I went from manual macro calling (clicking on the ribbon) to a visual, checkbox-and-button based interface.

Also, as another fun fact, the cells the copy-paste sub throws an error on almost always end up getting deleted (presumably by the sub itself). That's also never happened before, and I don't understand why an error like that would only just manifest itself now.

Thanks for any insight you might be able to provide.

 

[jimrward]

here is a snip of code I use to copy to the clipboard, this can then be copied to anything outside of excel and inserted with control V or paste

Dim myData As DataObject

Set myData = New DataObject
myData.SetText "calendar alert added my outlook"
myData.PutInClipboard

you may need the reference to dataobject, which from memory is automatic if you start a new project and insert a userform, and then delete the project, unless of course you wish to use the form, this will load the references for Microsoft Dataobject V 2.0

 

[vaniificat]

Thanks for your reply. Unfortunately, if you'll look up in the second code block, that's almost exactly the code I used, which is giving me problems. The "MyData.putinclipboard" command, specifically.

 

[jerrodmason]

Thanks for your reply. Unfortunately, if you'll look up in the second code block, that's almost exactly the code I used, which is giving me problems. The "MyData.putinclipboard" command, specifically.

I also have had routines that once worked fine no longer do, with a strange twist. Here's an example:

Function RangeToText(r As Range) As String
Set MyData = New MSForms.DataObject
r.Copy
MyData.GetFromClipboard
RangeToText = MyData.GetText
Set MyData = Nothing
End Function

The procedure stops at the red line and displays the "OpenClipboard Failed" popup. If I then press Debug and then F5 (VBA Run), it works just fine!

Until the next time, that is. Restarting Excel and/or Windows 7 does not help. I'm at wits end on this.

Thanks for any help.

 

[DrNicket]

I also have had routines that once worked fine no longer do, with a strange twist. Here's an example:

Function RangeToText(r As Range) As String
Set MyData = New MSForms.DataObject
r.Copy
MyData.GetFromClipboard
RangeToText = MyData.GetText
Set MyData = Nothing
End Function

The procedure stops at the red line and displays the "OpenClipboard Failed" popup. If I then press Debug and then F5 (VBA Run), it works just fine!

Until the next time, that is. Restarting Excel and/or Windows 7 does not help. I'm at wits end on this.

Thanks for any help.

I'm having the same problem with similar code. Also, When pasting, I'd like to keep my default signature, not overwrite it.
Here's the relevant snippet of my code:

Dim strPaste  As Variant
Dim DataObj As MSForms.DataObject
Set DataObj = New MSForms.DataObject
DataObj.GetFromClipboard

strPaste = DataObj.GetText(1)
    
    With OutMail
        .To = Email 'extracted from excel
        .BCC = "name@place.com"
        .Subject = Subj 'extracted from other level of code
        .BodyFormat = 2 '1=Plain text, 2=HTML 3=RichText
        .Body = strPaste
        .Display
    End With

Please help