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.
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 & “</tr>” & 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.
Larry:
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!
4 December 2004, 11:20 amLarry
Stephen:
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?
21 April 2005, 11:52 amSudhan:
Great solution..
6 August 2005, 4:02 amI 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..
Peter Jorgensen:
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:\Windows\System32\FM20.DLL, it will be in the references list and you can access the DataObject perfectly. Solved a problem for me :).
9 October 2005, 1:30 amwww.cubetoon.com » Blog Archive » Acquiring data from MS Excel using DDE:
[…] 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 […]
2 July 2007, 2:46 amGerardo Portillo:
thanks, thats helps me a lot!
30 April 2008, 1:22 pmFred Cumming:
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
5 November 2008, 4:21 pmfred
Dick Kusleika:
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