Excel VBA: Worksheet.PasteSpecial

Rendering HTML text in a cell

We need to add a reference to MS Forms which can be created by executing the following line in the immediate window.

application.vbe.activeVBProject.references.addFromGuid "{0D452EE1-E08F-101A-852E-02608C4D0BB4}", 2, 0

The following function (sub) can then be used to render a given HTML in the specified cell:

option explicit

public sub htmlToCell(html as string, cel as range)

   application.enableEvents = false

   dim objData as new msForms.dataObject
   objData.setText html
   objData.putInClipboard
   cel.select
   cel.parent.pasteSpecial format := "unicode text"

   application.enableEvents = true

end sub

Ideas to test the function:

htmlToCell "<html><table><tr><td>x</td><td>xxxxxx</td></tr><tr><td>yyyyyy</td><td>yy</td></tr></html>", cells(5,3)
htmlToCell "<html><i>Italic</i>, <b>bold</b> and <font color=red>red</font></html>", cells(10, 10)

Excel VBA: Worksheet.PasteSpecial

Rendering HTML text in a cell

See also