vba HTML 到 Excel 格式的转换——break 和 li 在同一个单元格中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10035914/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
HTML to Excel formatting conversion - break and li in the same cell
提问by Kevin McGovern
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)?
我在本周早些时候发布了一个关于 HTML 到 Excel 转换的问题，对我来说效果很好。我得到的示例宏代码在将代码从 HTML 格式转换为 Excel 单元格方面做得很好（感谢 Siddharth Rout！）。我现在遇到的问题似乎在任何地方都找不到答案，这与 IE 对象如何处理 Excel 中的段落、分隔符和列表项有关。p、br 和 li 将文本移动到原始单元格下方的单元格中，覆盖这些单元格中的所有数据。有没有办法让 HTML 块只显示在一个单元格中（意味着每个新行标记只会在同一个单元格中创建一个新行）？
VBA code
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
Sample HTML
示例 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 Outputthat is displaying on multiple rows (would like to display on multiple rows in one cell - similar to the way shift+enter works)
在多行上显示的示例输出（希望在一个单元格中的多行上显示 - 类似于 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*
回答by Siddharth Rout
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 insertthem in sheet1 so that you data is not overwritten. See snapshot.
逻辑：不是将其粘贴在同一张表中，而是将其粘贴到临时表中，然后复制这些行并将它们插入到 sheet1 中，这样您的数据就不会被覆盖。见快照。
SNAPSHOT:
快照：
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
HTH
