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>”
sText = sText & vbNewLine & vbTab & “” & vbNewLine
sText = sText & “</table>”
‘Put sText into the DataObject
‘Put the data in the DataObject into the Clipboard
Set doClip = Nothing
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.