PutInClipboard Office 365

[gallen]

My company has just upgraded to 365.

My previous version was Office 2013

I had code that copied the text from a selected cell to the clipboard. This now doesn't work in 365

The code is as below:

Public Sub AddToClipboard(s As String)
   
Dim oCopy As DataObject


        Set oCopy = New DataObject
        oCopy.SetText s
        oCopy.PutInClipboard
    
End Sub

This has worked fine until 365 was installed.

I tried late binding using similar code I found from Google:

Sub CopyText(Text As String)
    Dim o As Object
    Set o = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
    o.SetText Text
    o.PutInClipboard
    Set o = Nothing
End Sub

This has the same result. When I paste anything I've copied it shows "??"

I've debugged line by line and the correct text is being added in the 'SetText' line but pasting gives me the 2 question marks.

Any clues?

 

[RoryA]

It's actually nothing to do with Office. There's a bug in Windows 8 and later that causes this to happen if you have File Explorer windows open. You should use Windows API calls to put text in the clipboard to be safe.

 

[ISY]

Hi

There's An Excel Office Bug!
https://www.thespreadsheetguru.com/blog/2015/1/13/how-to-use-vba-code-to-copy-text-to-the-clipboard

 

[gallen]

ahhh! Of course. I forgot to mention they upgraded to Win 10 from Win7. Many thanks

 

[Jaafar Tribak]

Another clipboard tool that might work :

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

Sub Test()
    SetClipBoardText "Hello"
    MsgBox GetClipBoardText
    ClearClipBoardText
    MsgBox GetClipBoardText
End Sub

 

[gallen]

Another clipboard tool that might work :

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

Sub Test()
    SetClipBoardText "Hello"
    MsgBox GetClipBoardText
    ClearClipBoardText
    MsgBox GetClipBoardText
End Sub

This works a treat. Nice workaround.... Thank you