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.
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.
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.
 |
 |
and looks like this in a browser
If you want to see a real life example of this technique, see Juan Pablo Gonz lez’s VB HTML Maker.
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
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?
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..
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 :).
[…] to the Daily Dose of Excel where I found the required tip to add only text to the clipboard. My initial attempt to just copy […]
thanks, thats helps me a lot!
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
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:
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
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
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.
So great
Thanks
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
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.
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
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.
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.