Pasting HTML string with line breaks into a single Excel cell

Question

I'm trying to insert HTML/RTF formatted text into a single Excel cell, preserving text formatting (colour, bold, italic etc.).

The problem is that if the text contains line breaks (<br>), parts of the text between the breaks is inserted into separate cells.

screenshot of the problem

I tried <b style="mso-data-placement:same-cell;" />, but it seems to be ignored by Excel's parser and treated like a normal <br>.

A lot of topics on forums say that mso-data-placement:same-cell lets you have new line breaks inside a cell, but in my case this style is ignored.

How do I achieve text formatting and line breaks when pasting text into a single cell?

My code (simplified a bit):

Private Sub Worksheet_Change2(ByVal Target As Range, ByVal sht As Worksheet)
Dim objData As MSForms.DataObject
Dim sHTML As String

Application.EnableEvents = False
Set objData = New MSForms.DataObject
sHTML = "<html>ABC<br>EFG" & "<br style=" & Chr(34) & "mso-data-placement:same-cell;" & Chr(34) & " />" & "XYZ</html>"
objData.SetText  sHTML
objData.PutInClipboard

sht.PasteSpecial Format:="Unicode Text" ', NoHTMLFormatting:=False
Application.EnableEvents = True
End Sub

Unknown Unknown · Accepted Answer · 2017-10-26T22:43:56

For <br style="mso-data-placement:same-cell;"/> to work it needs to be in a <TD> in a <TABLE>.

Private Sub Worksheet_Change2(ByVal Target As Range, ByVal sht As Worksheet)
    Const br = "<br style=""mso-data-placement:same-cell;""/>"
    Application.EnableEvents = False
    Dim objData As MSForms.DataObject
    Set objData = New MSForms.DataObject

    With CreateObject("System.Text.StringBuilder")
        .Append_3 "<HTML>"
        .Append_3 "<TABLE>"
        .Append_3 "<TR>"
        .Append_3 "<TD>"
        .Append_3 "ABC"
        .Append_3 br
        .Append_3 "EFG"
        .Append_3 br
        .Append_3 "XYZ"
        .Append_3 "</TD>"
        .Append_3 "</TR>"
        .Append_3 "</TABLE>"
        .Append_3 "</HTML>"
        objData.SetText .ToString
        objData.PutInClipboard   
    End With

    sht.Paste
    Application.EnableEvents = True
End Sub

Note: It might be helpful to use the TagBuilder class that I have in my CodeReview post Creating HTML using a Builder Pattern

Pasting HTML string with line breaks into a single Excel cell

1 Answers