Copy to Clipboard

[richardlp]

In VBA I am having issues copying a string to clipboard. I have read most post; however, I get error messages. I am on a company computer, so I am assuming that there is some security setting causing my issue.

So, hopefully somebody might be able to help me out. Currently, I am trying to us API code. I am getting message "Can't find DLL entry point GlobalAlloc in kernel32.

 

[xenou]

In Excel vba I have always used the forms data object without too much trouble. Not sure what you are doing with api code - could be a 32 bit vs 64 bit thing ...
Example of data object clipboard:

VBA-Excel: Putting Text In The Windows Clipboard

For working with Windows Clipboard you need DataObject, the object in MSForms library. It provides support for text-string. For that you must add the reference “Microsoft Forms 2.0 Object Library” How to add “Microsoft Forms 2.0 Object Library” Now, For …

excel-macro.tutorialhorizon.com

 

[richardlp]

Thank you; however, when I paste, there is nothing that paste. I know the notepad file received something because it is asking me if I want to save while closing. Otherwise, notepad is blank.

 

[ZVI]

Hi,
Try the solution from Rory - VBA Post to Clipboard
In some configurations DataObject doesn't work if Explorer's window is open, it seems an old bug (2013+) but it's still here.

 

[richardlp]

Hi ZVI,

I get message, "Run-time error '429'. ActiveX component can't create object" upon running the code.

 

[ZVI]

Hi Richard,
Try using early binding instead of late binding.
In VBE-Tools-References set the reference to Microsoft HTML Object Library and use the below code:

Option Explicit
' Set via VBE-Tools-References the reference to Microsoft HTML Object Library

Function SetClipBoardText(ByVal Text As Variant) As Boolean
  With New HTMLDocument
    SetClipBoardText = .parentWindow.ClipboardData.SetData("Text", Text)
  End With
End Function

Function GetClipBoardText() As String
  On Error Resume Next
  With New HTMLDocument
    GetClipBoardText = .parentWindow.ClipboardData.GetData("Text")
  End With
End Function

Function ClearClipBoardText() As Boolean
  With New HTMLDocument
    ClearClipBoardText = .parentWindow.ClipboardData.clearData("Text")
  End With
End Function

' === Tests ===

Sub Test_SetClipBoardText()
  Dim Txt As String
  Txt = "Any text"
  Call SetClipBoardText(Txt)
  ' Use Ctrl+V to paste text from the Clipboard
End Sub

Sub Test_GetClipBoardText()
  ' Copy any text via Ctrl+C to the Clipboard, or uncomment the below line
  'Call SetClipBoardText("MyText")
  Dim Txt As String
  Txt = GetClipBoardText()
  Debug.Print Txt
End Sub

Sub Test_ClearClipBoardText()
  ' Copy any text via Ctrl+C to the Clipboard or uncomment the below line
  'Call SetClipBoardText("MyTextForTesting")
  Dim Ret As Boolean
  Ret = ClearClipBoardText
  Debug.Print Ret
  ' Use Ctrl+V to paste empty string from the Clipboard
End Sub

An alternative method is using API calls, but it is not compact.