vba VBA将html导入表中的结果拆分为excel
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17095050/
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
VBA spliting results from html imported table into excel
提问by John Malko
Hi I am importing a whole table from a website to excel string:
嗨，我正在从网站导入整个表格到 excel 字符串：
Dim fST As String
fST = Doc.getElementsByTagName("table")(0).innerText
after that I would like to split the table inside excel cells and the splitting to be done using the <td>tags from the html table, or at least this is the option for which I think can be done so the imported table will be the same inside excel once it is imported every value will be inside individual cell.
之后，我想在 excel 单元格中拆分表格，并使用<td>html 表格中的标签进行拆分，或者至少这是我认为可以完成的选项，因此导入的表格在 excel 中将相同导入后，每个值都将位于单个单元格内。
Let me know thanks.
让我知道谢谢。
Here is the Whole conde that I am using:
这是我正在使用的整个 conde：
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = Range("URL").Row And _
Target.Column = Range("URL").Column Then
Dim IE As New InternetExplorer
IE.Visible = True
IE.navigate Application.ActiveSheet.Range("URL")
Do
DoEvents
Loop Until IE.readyState = READYSTATE_COMPLETE
Dim Doc As HTMLDocument
Set Doc = IE.document
Dim tbl, trs, tr, tds, td, r, c
Set tbl = Doc.getElementsByTagName("table")(0)
Set trs = tbl.getElementsByTagName("tr")
For r = 1 To trs.Count
Set tds = trs(r).getElementsByTagName("td")
For c = 1 To tds.Count
ActiveSheet.Cells(r, c).Value = tds(c).innerText
Next c
Next r
IE.Quit
End If
End Sub
But it says error: Object doesn't support this property or method on the following line: For r = 1 To trs.Count
但它说错误：对象不支持以下行中的此属性或方法： For r = 1 To trs.Count
回答by Tim Williams
EDIT: tested example
编辑：测试示例
Sub Tester()
Dim IE As Object
Dim tbls, tbl, trs, tr, tds, td, r, c
Set IE = CreateObject("internetexplorer.application")
IE.navigate "http://www.w3schools.com/html/html_tables.asp"
Application.Wait Now + TimeSerial(0, 0, 4)
Set tbls = IE.Document.getElementsByTagName("table")
For r = 0 To tbls.Length - 1
Debug.Print r, tbls(r).Rows.Length
Next r
Set tbl = IE.Document.getElementsByTagName("table")(5)
Set trs = tbl.getElementsByTagName("tr")
For r = 0 To trs.Length - 1
Set tds = trs(r).getElementsByTagName("td")
'if no <td> then look for <th>
If tds.Length = 0 Then Set tds = trs(r).getElementsByTagName("th")
For c = 0 To tds.Length - 1
ActiveSheet.Range("B4").Offset(r, c).Value = tds(c).innerText
Next c
Next r
End Sub
回答by Rrgg
I looked all over for the answer to this question, too. I finally found the solution by talking to a coworker which was actually through recording a macro.
我也到处找这个问题的答案。我终于通过与同事交谈找到了解决方案，这实际上是通过录制宏。
I know, you all think you are above this, but it is actually the best way. See the full post here: http://automatic-office.com/?p=344In short, you want to record the macro and go to data --> from web and navigate to your website and select the table you want. Tell excell which cell to put it in and thats it!
我知道，你们都认为自己在这之上，但这实际上是最好的方式。请参阅此处的完整帖子：http: //automatic-office.com/?p=344简而言之，您想要记录宏并转到数据 --> 从网络并导航到您的网站并选择您想要的表格。告诉excel将它放在哪个单元格中，就是这样！
I have used the above solutions "get element by id" type stuff in the past, and it is great for a few elements, but if you want a whole table, and you aren't super experienced, just record a macro. don't tell your friends and then reformat it to look like your own work so no one knows you used the macro tool ;)
我过去使用过上述解决方案“按 id 获取元素”类型的东西，它对少数元素非常有用，但是如果你想要一个完整的表格，而且你不是非常有经验，只需录制一个宏。不要告诉你的朋友，然后重新格式化它看起来像你自己的作品，所以没有人知道你使用了宏工具；)
