Skip to content Skip to sidebar Skip to footer
Home / Excel / Html / Vba

Pasting HTML String With Line Breaks Into A Single Excel Cell

Post a Comment
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

Solution 1:

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


Post a Comment for "Pasting HTML String With Line Breaks Into A Single Excel Cell"