Putting Text Into the Windows Clipboard

In VBA, you can put data into the Windows clipboard using the DataObject object. DataObject is in the Microsoft Forms 2.0 Library, so you’ll need to set a reference to that library to use it.

Clipboard1

I don’t know why it’s in the Forms library. It probably was developed as a way to copy and paste between ActiveX controls.

This object can be useful when you want to put the contents of the variable into the clipboard so the user can paste it wherever he wants. In this example, an HTML table is created from an Excel range to be pasted in Notepad or whatever you use to create html.

Sub MakeHTMLTable()

    Dim doClip As DataObject
    Dim sText As String
    Dim rCell As Range, rRow As Range
    Dim rSrc As Range
   
    Set doClip = New DataObject
    Set rSrc = Sheet1.Range(“A1:B5”)
   
    sText = “<table border=3>” & vbNewLine
   
    ‘Loop through the rows, then the cells
   For Each rRow In rSrc.Rows
        sText = sText & vbTab & “< tr >” & vbNewLine & String(2, vbTab)
        For Each rCell In rRow.Cells
            sText = sText & “<td>” & rCell.Text & “</td>”
        Next rCell
        sText = sText & vbNewLine & vbTab & “” & vbNewLine
    Next rRow
   
    sText = sText & “</table>”
   
    ‘Put sText into the DataObject
   doClip.SetText sText
    ‘Put the data in the DataObject into the Clipboard
   doClip.PutInClipboard
   
    Set doClip = Nothing
   
End Sub

When the sub finishes, the text from sText is in the clipboard ready for pasting.

Clipboard2 Clipboard3

and looks like this in a browser

Clipboard4

If you want to see a real life example of this technique, see Juan Pablo Gonz lez’s VB HTML Maker.

Posted in Uncategorized

16 thoughts on “Putting Text Into the Windows Clipboard

  1. Dick,
    You are a God-send! I have been struggling with copying text from Excel to the clipboard (so that it could be pasted into a database application query) and had been totally unsuccessful until I read your post. I modified it to examine the active sheet and then to grab the information from the Selection object. Works beautifully!

    Kudos!
    Larry

  2. Someone on Experts-Exchange recommended this. This works for Excel, but any idea as to how to get it to work for Access 2002? I can’t find that object library listed in the VBA references for Access. Is there a way to import it from the Excel references? Any ideas? Or another way to do this in Access?

  3. Great solution..
    I have a problem associated with this case..
    When i copy a cell and paste it in a range it gets pasted for the first time and from the next time onwards it does not get pasted..

  4. I found a solution for access…. you can still read the Forms 2.0 library, it’s just not available by default in Access because access has its own forms library. Simply pull up the references and hit the browse button, and at C:WindowsSystem32FM20.DLL, it will be in the references list and you can access the DataObject perfectly. Solved a problem for me :).

  5. Hi Dick

    I was at the User Conference in Sydney earlier this year. I had a great time.

    I used your DataObject idea to write some VBA that gets the formulae from a selection of cells and pastes them to the clipboard.

    The reason I want to do this is so that I can paste them to my email program for transmission to text based Lists like Excel-L.

    The output looks like this :-

    $A$1:door A
    $B$1:door B
    $C$1:door C
    $D$1:Initial Choice
    $E$1:No Switch
    $F$1:Switch
    $A$2:=RANDBETWEEN(0,1)
    $B$2:=IF(A2=0,RANDBETWEEN(0,1),0)
    $C$2:=IF(SUM(A2:B2)=0,1,0)
    $D$2:=CHOOSE(RANDBETWEEN(1,3),”A”,”B”,”C”)
    $E$2:=IF(INDIRECT(D2&ROW())0,”Win”,”Lose”)
    $F$2:=IF(E2=”Win”,”Lose”,”Win”)
    $H$8:switch wins
    $I$8:=COUNTIF(F:F,”Win”)
    $J$8:=I8/SUM(I8:I9)
    $K$8:=NOW()
    $H$9:noswitch wins
    $I$9:=COUNTIF(E:E,”Win”)
    $J$9:=I9/SUM(I8:I9)

    Now I need to write a little decoder routine that gets data from the clipboard and builds a sheet based on these formulae.

    I can do this by pasting to a sheet, importing the new range as an array and then processing the array but that seems a bit clumsy.

    Can I user the DataObject to GET stuff from the clipboard as well? Or is there some other trick I need to know!

    Cheers
    fred

  6. Hey Fred. Did you give me some books to take home to my boy? He loved them, so thanks if that was you.

    You need the GetFromClipboard and GetText methods. Here’s what I came up with:

    Sub DecodeL()
       
        Dim doClip As DataObject
        Dim sText As String
        Dim vaLines As Variant
        Dim vaCells As Variant
        Dim i As Long, j As Long
        Dim sFormula As String
       
        Set doClip = New DataObject
       
        doClip.GetFromClipboard
        sText = doClip.GetText
       
        vaLines = Split(sText, vbNewLine)
        For i = LBound(vaLines) To UBound(vaLines)
            vaCells = Split(vaLines(i), “:”)
            sFormula = “”
            For j = 1 To UBound(vaCells)
                sFormula = sFormula & vaCells(j) & “:”
            Next j
            sFormula = Left$(sFormula, Len(sFormula) – 1)
            Sheet1.Range(vaCells(0)).Formula = sFormula
        Next i
       
        Set doClip = Nothing
       
    End Sub
  7. Hi Dick, many thanks! I was able to put a copy-text button on a worksheet, with the help of your tutorial! One thing is that, I did not notice the “Microsoft Forms 2.0 Library” in the References dialog box in Office Excel 2007. So I had to manually browse the FM20.DLL file as seen on the above screenshot of the References dialog box. Thanks a lot! :)

    – Ashis

  8. I’m pretty sure it’s there Ashis, but I’ve only ever used the Ultimate version in English, so there’s a lot I don’t know. If you add a Userform, it gets references automatically. I’d be curious to know if it shows up when you add a userform.

  9. Hi Dick,
    Thanks for the advice above it has help me figure out what is happening in the program but I am still unsure on how I can edit it. I am a pharmacist and work with FileMakerPro and Excel to create (FileMakerPro) new formulas for medication that need to be compounded and record the data in Excel. I practically inherited these macros and have been trying to figure them out to improve them so I can insert other items into the work sheet.

    Currently when I export data from FileMakerPro it collects information from figure3p. which is essentially a table but it only collects the name in column one (name of the Drug) that are given the suffix of grams (g) and exports that to excel where I use excel to add more information onto the worksheet.

    I was hoping that I could firstly include all the Drug names (the drugs with mLs) to be imported. (I believe this is a fileMakerPro issue)

    And Secondly I was wanting to grab the information from the 3rd Column and export it as well. However, this coloumn had a numberical number but also comes with g (for grams) after the number automatically and I am just after the number.

    This is one part of the macro that deals with this area:

    Sub InsertTareAndName()
    Dim doClip As DataObject
    Set doClip = New DataObject
    doClip.GetFromClipboard
    Dim sText As Variant
    sText = doClip.GetText
    Selection.Value = “Tare”
    ActiveCell.Offset(1, 0).Select
    Selection.Value = sText
    Set doClip = Nothing
    ActiveCell.Offset(0, 2).Select
    ActiveCell.FormulaR1C1 = _
    “=IF(RC[-1]=(VLOOKUP(RC[-2],’Drug Names’!R1C3:R400C11,9,FALSE)),””Correct””,””Error””)”
    ActiveCell.Offset(0, 2).Select
    ActiveCell.Select
    ActiveCell.FormulaR1C1 = “=VLOOKUP(RC[-4],’Drug Names’!R1C3:R350C5,2,FALSE)”
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = “=VLOOKUP(RC[-1],’Drug Names’!R2C4:R350C5,2,FALSE)”
    ActiveCell.Offset(1, -5).Select
    End Sub

    This extra information from the vlookup is just additive material that I am including to make my information better and isn’t connected to the File Maker Pro.

    I was wondering if I could grab your assistance in changing this marco to add this extra information from FileMaker

    Thanks Glen

  10. hi
    i aapricate you. but i don,t have Microsoft Forms 2.0 Library ocx file so can you suggest me for how can i get these file.

  11. Is there a way to get the number of rows in the current clipboard to VBA?

    Here is what I’m trying to do:
    1. Copy text to clipboard (from 1 to 20 rows or so; and 8 columns
    2. Insert that number of rows into the Excel Spreadsheet at the current location
    3. Paste the clipboard to the excel sheet

    Thanks for the Help!

    Fred

  12. Fred: If you have access to the text before it gets put in the clipboard, it’s probably easiest to determine the number of lines from that. Something like

    lRows = Len(sText) - Len(Replace(sText, vbCR, ""))

    Once it's in the clipboard, you probably stuck pasting it to an unused range, counting the rows, then re-copying it.

  13. I don’t know why it’s not working for me. I used the same reference(It’s default actually) but it still will not paste into a notepad which is my objective. Here is my code:

    Private Sub genCCF_Click()
    Dim DataObj As New DataObject
    Dim str As String
    Dim visit As String
    Dim dispo As String
    Dim opsys As String
    Dim prob As String
    Dim i As Integer
    defineControls
    i = bugsproject.TrackerForm.MultiPage1.SelectedItem.Index

    ‘Set DataObj = New DataObject

    visit = bugsproject.TrackerForm.MultiPage1.Pages(i).Controls.Item(VisitID).value
    dispo = bugsproject.TrackerForm.MultiPage1.Pages(i).Controls.Item(Disposition).value
    opsys = bugsproject.TrackerForm.MultiPage1.Pages(i).Controls.Item(OS).value
    prob = bugsproject.TrackerForm.MultiPage1.Pages(i).Controls.Item(Problem).value

    If visit = “” Or dispo = “” Or opsys = “” Or prob = “” Then
    MsgBox “Fill out all *required fields”
    Else
    str = “ADO | ” & dispo & ” | ” & visit & ” | ” & opsys & ” | ” & prob
    ‘copy the value to a cell then use selection.copy to copy it to clipboard

    ‘DataObj.SetText str
    ‘DataObj.PutInClipboard

    ‘Set DataObj = Nothing

    End If
    End Sub

    Still it pastes nothing. I don’t understand why.


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.