HTML to Excel formatting conversion - break and li in the same cell

Question

I posted a question earlier in the week regarding HTML to Excel conversion that worked well for me. The sample macro code I was given did a great job of converting the code from HTML format into an Excel cell (thanks Siddharth Rout!). The problem I'm running into now and can't seem to find an answer to anywhere has to do with how the IE object handles paragraphs, breaks, and list items in Excel. p, br, and li move the text into cells below the origin cell, overwriting any data that was in those cells. Is there any way to get the block of HTML to display in only one cell(meaning each new line tag would just create a new line in the same cell)?

VBA code

Sub Sample()
    Dim Ie As Object

    Set Ie = CreateObject("InternetExplorer.Application")

    With Ie
        .Visible = False

        .Navigate "about:blank"

        .document.body.InnerHTML = Sheets("Sheet1").Range("A1").Value

        .document.body.createtextrange.execCommand "Copy"
        ActiveSheet.Paste Destination:=Sheets("Sheet1").Range("A1")

        .Quit
    End With
End Sub

Sample HTML

<p>  Here are some possible uses:</p>  <ul>  <li><font color = "red"> syntax highlighting code snippets</font></li>  <li style ="font-weight:bold; color: orange">validating credit card numbers, phone numbers, and zip codes</li>  <li style = "font-style: italic">styling email addresses and tags</li>  </ul>

Sample Output that is displaying on multiple rows (would like to display on multiple rows in one cell - similar to the way shift+enter works)

Here are some possible uses:



syntax highlighting code snippets

**validating credit card numbers, phone numbers, and zip codes**

*styling email addresses and tags*

Siddharth Rout Siddharth Rout · Accepted Answer · 2012-04-05T22:17:18

I am not sure if you can do that (I could be wrong). But if it is just a problem of your data being overwritten then here is an alternative :)

LOGIC: Instead of pasting it in the same sheet, paste it in a temp sheet and then copy those rows and insert them in sheet1 so that you data is not overwritten. See snapshot.

SNAPSHOT:

enter image description here

CODE:

Sub Sample()
    Dim ws As Worksheet, wstemp As Worksheet
    Dim Ie As Object
    Dim LastRow As Long

    Set Ie = CreateObject("InternetExplorer.Application")

    Set ws = Sheets("Sheet1")

    '~~> Create Temp Sheet
    Set wstemp = Sheets.Add

    With Ie
        .Visible = True

        .Navigate "about:blank"

        '~~> I am assuming that the data is in Cell A1
        .document.body.InnerHTML = ws.Range("A1").Value

        '~~> Deleting the row which had the html string. I am assuming that it was in Row 1
        ws.Rows(1).Delete

        .document.body.createtextrange.execCommand "Copy"
        wstemp.Paste Destination:=wstemp.Range("A1")

        '~~> Find the last row in the temp sheet
        LastRow = wstemp.Cells.Find(What:="*", After:=wstemp.Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

        '~~> Copy that data
        wstemp.Rows("1:" & LastRow).Copy

        '~~> insert it in Sheet1
        ws.Rows(1).Insert Shift:=xlDown

        .Quit
    End With

    '~~> Delete Temp sheet
    Application.DisplayAlerts = False
    wstemp.Delete
    Application.DisplayAlerts = True

End Sub

HTH

HTML to Excel formatting conversion - break and li in the same cell

1 Answers