HTML转Excel格式转换 - 在同一单元格中使用break和li

Question

我本周早些时候发布了一个关于HTML转换为Excel的问题，我得到的样例宏代码成功地将HTML格式的代码转换为Excel单元格（感谢Siddharth Rout！）。但我现在遇到的问题是，在Excel中IE对象如何处理段落、换行和列表项。p、br和li会将文本移动到原始单元格下方的单元格中，覆盖那些单元格中的任何数据。有没有办法让HTML块仅在一个单元格中显示（也就是说，每个新行标签只会在同一单元格中创建新行）？ VBA代码

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

示例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>  

示例输出显示在多行（希望在一个单元格中多行显示-类似于shift+enter的方式）

Translated:

Sample Output 在多个行上显示（希望在一个单元格中换行显示 - 类似于按下shift + enter键的方式）

Here are some possible uses:



syntax highlighting code snippets

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

*styling email addresses and tags*

- Kevin McGovern

Answer 1

我不确定你是否能够这样做（我可能错了）。但如果只是数据被覆盖的问题，这里有一个替代方案 :)

逻辑：不要将数据粘贴到同一张工作表中，而是将其粘贴到一个临时工作表中，然后将这些行插入到sheet1中，这样你的数据就不会被覆盖。请参见快照。

快照：

代码：

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

- Siddharth Rout