Excel VBA code to copy a specific string to clipboard

Question

I m trying to add a button to a spreadsheet that when clicked will copy a specific URL to my clipboard   I had a bit of knowledge of Excel VBA but it s been a while and I m struggling

User · Answer

This macro uses late binding to copy text to the clipboard without requiring you to set references. You should be able to just paste and go:

Sub CopyText(Text As String)
    'VBA Macro using late binding to copy text to clipboard.
    'By Justin Kay, 8/15/2014
    Dim MSForms_DataObject As Object
    Set MSForms_DataObject = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
    MSForms_DataObject.SetText Text
    MSForms_DataObject.PutInClipboard
    Set MSForms_DataObject = Nothing
End Sub

Usage:

Sub CopySelection()
    CopyText Selection.Text
End Sub

User · Answer

The simplest  Non Win32  way is to add a UserForm to your VBA project  if you don t already have one  or alternatively add a reference to Microsoft Forms 2 Object Library  then from a sheet module you can simply   With New MSForms DataObject      SetText  http   zombo com       PutInClipboard End With

User · Answer

Add a reference to the Microsoft Forms 2 0 Object Library and try this code  It only works with text  not with other data types   Dim DataObj As New MSForms DataObject   Put a string in the clipboard DataObj SetText  Hello   DataObj PutInClipboard   Get a string from the clipboard DataObj GetFromClipboard Debug Print DataObj GetText   Here you can find more details about how to use the clipboard with VBA

User · Answer

If the place you re gonna paste have no problem with pasting a table formating  like the browser URL bar   I think the easiest way is this   Sheets 1  Range  A1000   Value   string Sheets 1  Range  A1000   Copy MsgBox  Paste before closing this dialog   Sheets 1  Range  A1000   Value

User · Answer

If the url is in a cell in your workbook  you can simply copy the value from that cell   Private Sub CommandButton1 Click       Sheets  Sheet1   Range  A1   Copy End Sub    Add a button by using the developer tab  Customize the ribbon if it isn t visible    If the url isn t in the workbook  you can use the Windows API  The code that follows can be found here  http   support microsoft com kb 210216  After you ve added the API calls below  change the code behind the button to copy to the clipboard   Private Sub CommandButton1 Click       ClipBoard SetData   http   stackoverflow com   End Sub   Add a new module to your workbook and paste in the following code   Option Explicit  Declare Function GlobalUnlock Lib  kernel32   ByVal hMem As Long       As Long Declare Function GlobalLock Lib  kernel32   ByVal hMem As Long       As Long Declare Function GlobalAlloc Lib  kernel32   ByVal wFlags As Long       ByVal dwBytes As Long  As Long Declare Function CloseClipboard Lib  User32     As Long Declare Function OpenClipboard Lib  User32   ByVal hwnd As Long       As Long Declare Function EmptyClipboard Lib  User32     As Long Declare Function lstrcpy Lib  kernel32   ByVal lpString1 As Any       ByVal lpString2 As Any  As Long Declare Function SetClipboardData Lib  User32   ByVal wFormat      As Long  ByVal hMem As Long  As Long  Public Const GHND    amp H42 Public Const CF TEXT   1 Public Const MAXSIZE   4096  Function ClipBoard SetData MyString As String     Dim hGlobalMemory As Long  lpGlobalMemory As Long    Dim hClipMemory As Long  X As Long       Allocate moveable global memory      -------------------------------------------    hGlobalMemory   GlobalAlloc GHND  Len MyString    1        Lock the block to get a far pointer      to this memory     lpGlobalMemory   GlobalLock hGlobalMemory        Copy the string to this global memory     lpGlobalMemory   lstrcpy lpGlobalMemory  MyString        Unlock the memory     If GlobalUnlock hGlobalMemory   lt  gt  0 Then       MsgBox  Could not unlock memory location  Copy aborted         GoTo OutOfHere2    End If       Open the Clipboard to copy data to     If OpenClipboard 0 amp     0 Then       MsgBox  Could not open the Clipboard  Copy aborted         Exit Function    End If       Clear the Clipboard     X   EmptyClipboard         Copy the data to the Clipboard     hClipMemory   SetClipboardData CF TEXT  hGlobalMemory   OutOfHere2      If CloseClipboard     0 Then       MsgBox  Could not close Clipboard      End If  End Function

User · Answer

If you want to put a variable s value in the clipboard using the Immediate window  you can use this single line to easily put a breakpoint in your code   Set MSForms DataObject   CreateObject  new  1C3B4210-F441-11CE-B9EA-00AA006B1A69     MSForms DataObject SetText VARIABLENAME  MSForms DataObject PutInClipboard  Set MSForms DataObject   Nothing

User · Answer

To write text to  or read text from  the Windows clipboard use this VBA function  Function Clipboard  Optional s       Dim v  v   s   Cast to variant for 64-bit VBA support     With CreateObject  quot htmlfile quot       With  parentWindow clipboardData         Select Case True             Case Len s    setData  quot text quot   v             Case Else    Clipboard    getData  quot text quot           End Select     End With     End With End Function    Three examples of copying text to the clipboard  Clipboard  quot Excel Hero was here  quot  Clipboard var1  amp  vbLF  amp  var2 Clipboard 123   To read text from the clipboard  MsgBox Clipboard  This is a solution that does NOT use MS Forms nor the Win32 API  Instead it uses the Microsoft HTML Object Library which is fast and ubiquitous and NOT deprecated by Microsoft like MS Forms  And this solution respects line feeds  This solution also works from 64-bit Office  Finally  this solution allows both writing to and reading from the Windows clipboard  No other solution on this page has these benefits

[excel] Excel VBA code to copy a specific string to clipboard

Examples related to excel

Examples related to clipboard

Examples related to vba