VBA - Paste Multiple Lines of HTML into Single Cell

Question

I used another answer on SO to be able to convert an HTML string to displayed rich text in excel.

However, it comes with a nasty side effect of not being able to add data for multiple lines in a single cell (recommendation I found was to remove the paste logic).

Ideally, I'd like to NOT use the CreateObject for Internet Explorer in my solution, and just get the paste to work properly.

Here's the code that is found using a dictionary that does the paste to each cell.

How do I accomplish both the conversion of an HTML string to text AND paste multiple lines to a single cell?

            ' Sort By Years Descending
            Dim yearKey As Variant
            Dim firstYear As Boolean
            Dim cellData As String
            firstYear = True
            cellData = "<HTML><DIV>"
            For Each yearKey In stakeholderEvents(stakeholder).Keys

                If Not firstYear Then
                    cellData = cellData & "<DIV></DIV>" ' Add Extra Blank Line
                End If

                cellData = cellData & "<B>" & yearKey & "</B>" & "<UL>" ' Add Year


                ' Loop Through Events For Year
                Dim eventItem As Variant
                For Each eventItem In stakeholderEvents(stakeholder)(yearKey)

                    cellData = cellData & "<LI>" & eventItem & "</LI>"
                Next

                cellData = cellData & "</UL>"

                firstYear = False
            Next

            cellData = cellData & "<DIV></BODY></HTML>"

            Set clipboardData = CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
            clipboardData.SetText cellData
            clipboardData.PutInClipboard

            Sheet1.Activate

            'Sheet1.Range (Sheet1.Cells(rowIndex, stakeholderEventsColumn).Address)
            Sheet1.Cells(rowIndex, stakeholderEventsColumn).Select

            'Sheet1.Cells(rowIndex, stakeholderEventsColumn).Select
            Sheet1.PasteSpecial Format:="Unicode Text"

It's not clear exactly what you mean by "not being able to add data for multiple lines in a single cell". Also it would help if you could reference the other code you're using. — Tim Williams
– Tim Williams, Commented Aug 2, 2018 at 20:21
Can you add sample html that is being pasted or if it contains any br or tr tags — Slai
– Slai, Commented Aug 2, 2018 at 20:23
@Slai "<HTML><DIV><B>HELLO</B><UL><LI>EVENT1</LI><LI>EVENT2</LI></UL></DIV></HTML>" — Ctznkane525
– Ctznkane525, Commented Aug 2, 2018 at 22:03
This is where RPA solutions like Automation Anywhere would be of benefit to you. — JGFMK
– JGFMK, Commented Aug 7, 2018 at 9:45

Slai · Accepted Answer · 2018-08-04 02:16:33Z

2

+50

HTML alternative (reference Excel-friendly html: keeping a list inside a single cell) :

Set clipboardData = CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
clipboardData.SetText "<table><style>br {mso-data-placement:same-cell}</style><tr><td>" _
                    & "<b>Line 1</b><br>Line 2<br>Line 3"
clipboardData.PutInClipboard
Sheet1.Range("b2").PasteSpecial

XML alternative (the XML can be adjusted by analyzing the .Value(11) of a formatted cell) :

Dim c As Range
Set c = Sheet1.Range("b2")
c.Value = vbLf
c.Value(11) = Replace(c.Value(11), "<Data ss:Type=""String"">&#10;</Data>", _
    "<ss:Data ss:Type=""String"" xmlns=""http://www.w3.org/TR/REC-html40"">" & _
    "<B>Line 1</B>&#10;Line 2&#10;Line 3</ss:Data>")

edited Aug 4, 2018 at 2:16

answered Aug 4, 2018 at 2:04

Slai

23k5 gold badges49 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ctznkane525 Over a year ago

ill check the html alternative tonight...in some cases i have free form html...so alternative 2 wont work

Slai Over a year ago

The html seems to work only with <br> tags and XML with 
 encoded LF characters. I was thinking that they would need about the same effort to replace the html tags, but I suppose XML might be trickier as it's stricter to what it allows. Another alternative might be to embed a WebBrowser ActiveX control, or if the html can be pasted as an image.

Ctznkane525 Over a year ago

ill try div {mso-data-placement:same-cell} to add to style and see if that works...i can always try to replace the div tags that i have in other examples too

Ctznkane525 Over a year ago

thank you for spending the time on this...i appreciate it

Zack · Accepted Answer · 2018-08-03 15:52:49Z

0

I think the problem is that you're using HTML for formatting, and Excel is always going to paste the HTML so that block-level elements go into different cells.

Another approach would be to use Excel's built-in formatting instead of using HTML tags. This will also let you eschew using the Clipboard object. Here's an example of adding some text to a range, and then formatting some of the text differently (i.e. bolding):

    Public Sub PopulateHtmlData()
        Dim cell As Excel.Range

        Dim s1 As String, s2 As String
        s1 = "Item 1"
        s2 = "Item 2"

        Set cell = Excel.Range("$A$1")
        ' use Chr(10) to add a new line in the cell
        cell.Value = s1 & Chr(10) & s2

        cell.Characters(0, Len(s1) - 1).Font.Bold = True
    End Sub

answered Aug 3, 2018 at 15:52

Zack

2,3511 gold badge10 silver badges13 bronze badges

3 Comments

Ctznkane525 Over a year ago

its dynamic data...so i dont know what the format will be

Zack Over a year ago

You just have to keep track of where each part of the string starts and stops (e.g. have a dictionary of positions in the text that need additional formatting).

Ctznkane525 Over a year ago

i also have another cell with free form html data...so i need something more dynamic

Collectives™ on Stack Overflow

VBA - Paste Multiple Lines of HTML into Single Cell

2 Answers 2

4 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related