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, Aug 02 '18 at 20:21
Can you add sample html that is being pasted or if it contains any br or tr tags — Slai, Aug 02 '18 at 20:23
This is where RPA solutions like Automation Anywhere would be of benefit to you. — JGFMK, Aug 07 '18 at 09:45

Slai · Accepted Answer · 2018-08-04T02:16:33.743

2

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 04 '18 at 02:16

answered Aug 04 '18 at 02:04

Slai

22,144
5
45
53

ill check the html alternative tonight...in some cases i have free form html...so alternative 2 wont work – Ctznkane525 Aug 04 '18 at 11:43
The html seems to work only with `
` 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](https://stackoverflow.com/questions/35024440/how-to-make-microsoft-web-browser-object-work-in-excel-2016), or if the html can be pasted as an image. – Slai Aug 04 '18 at 12:40
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 Aug 04 '18 at 14:08
thank you for spending the time on this...i appreciate it – Ctznkane525 Aug 05 '18 at 00:27

score 0 · Answer 2 · answered Aug 03 '18 at 15:52

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 03 '18 at 15:52

Zack

2,220
1
8
12

its dynamic data...so i dont know what the format will be – Ctznkane525 Aug 03 '18 at 16:47
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). – Zack Aug 03 '18 at 16:51
i also have another cell with free form html data...so i need something more dynamic – Ctznkane525 Aug 03 '18 at 16:53

VBA - Paste Multiple Lines of HTML into Single Cell

2 Answers2