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>. 

PrivateSub Worksheet_Change2(ByVal Target As Range, ByVal sht As Worksheet)
    Const br = "<br style=""mso-data-placement:same-cell;""/>"
    Application.EnableEvents = FalseDim 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   
    EndWith

    sht.Paste
    Application.EnableEvents = TrueEndSub

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"