Excel VBA：从在线HTML表中获取内容

can anybody pleas show me part of VBA code, which will get text "hello" from this example online HTML table? first node will be found by his ID (id="something").

任何人都可以请求我显示VBA代码的一部分,这将从这个示例在线HTML表中获得文本“hello”吗?第一个节点将通过他的ID(id =“something”)找到。

...
<table id="something">
  <tr>
    <td><TABLE><TR><TD></TD></TR><TR><TD></TD></TR></TABLE></td><td></td>
  </tr>
  <tr>
    <td></td><td></td><td>hello</td>
  </tr>
...

i think it will be something like child->sibling->child->sibling->sibling->child, but I don't know the exact way.

我认为它会像child-> sibling-> child-> sibling-> sibling-> child,但我不知道确切的方法。

EDIT updated code tags are CAPITALS. so if I use getElemenetsById("something").getElemenetsByTagName('tr') it get only two tr tags to collection, or four (with tags which are deeper children)?

EDIT更新的代码标签是CAPITALS。所以,如果我使用getElemenetsById(“something”)。getElemenetsByTagName('tr')它只能收集两个tr标签,或者四个(标签是更深的孩子)?

2 个解决方案

#1

If you did search for an answer, you might want to broaden your scope next time. There are plenty of questions and answers that deal with DOM stuff and VBA.

如果您确实搜索了答案,则可能希望下次扩大范围。有很多问题和答案都涉及DOM的东西和VBA。

Use getElementById on HTMLElement instead of HTMLDocument

在HTMLElement上使用getElementById而不是HTMLDocument

While the question (and answers) aren't exactly what you want, it will show you how to create something you can work with.

虽然问题(和答案)并不完全符合您的要求,但它会告诉您如何创建可以使用的内容。

You'll need to use a mixture of getElementById() and getElemenetsByTagName() to retrieve your desired "hello"

你需要使用getElementById()和getElemenetsByTagName()的混合来检索你想要的“你好”

eg: Document.getElementById("something").getElementsByTagName("tr")(1).getElementsByTagName("td")(2).innerText

Get the element "something"

得到元素“东西”

Inside "something" get all "tr" tags (specifically the one at index 1)

在“something”里面得到所有“tr”标签(特别是索引1处的标签)

Inside the returned tr tag get all "td" tags (specifically the one at index 2)

在返回的tr标签内获取所有“td”标签(特别是索引2处的标签)

Get the innerText of the previous result

获取上一个结果的innerText

These objects use a 0 based array so the first item is item(0).

这些对象使用基于0的数组,因此第一项是项(0)。

Update

document.getElementById() will return an (singular) IHTMLElement (which will include all of its children) or nothing/null if it does not exist.

document.getElementById()将返回一个(单数)IHTMLElement(包含其所有子节点)或没有/ null(如果它不存在)。

document.getElementsByTagName() will return a collection of IHTMLElement (again, each element will include all of its children). (or an empty collection if none exist)

document.getElementsByTagName()将返回IHTMLElement的集合(同样,每个元素将包含其所有子元素)。 (如果不存在,则为空集合)

document.getElementsByTagName("tr") this will return all tr elements inside the "document" element.

document.getElementsByTagName(“tr”)这将返回“document”元素内的所有tr元素。

document.getElementsByTagName("tr")(0) will return the first (singular) IHTMLElement from the collection. (note the index at the end?)

document.getElementsByTagName(“tr”)(0)将从集合中返回第一个(单数)IHTMLElement。 (注意结尾的索引?)

There is no (that i could find) "sibling" feature of the InternetExplorer object in VBA, so you'd have to do it manually using the child index.

在VBA中没有(我能找到)InternetExplorer对象的“兄弟”功能,所以你必须使用子索引手动完成。

Using the DOM Functions is the clean way to do it. Its much clearer than just looking at a chain "Element.Children(0).children(1).children(2)" as you've no idea what the index means without manually looking it up.

使用DOM函数是一种干净的方法。它比仅仅查看链“Element.Children(0).children(1).children(2)”更清晰,因为你不知道索引意味着什么而不用手动查找它。

#2

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=344 In short, you want to record the macro and go to data --> from web and navigate to your website and select the table you want.

我知道,你们都认为自己超越了这一点,但这实际上是最好的方式。请在此处查看完整帖子:http://automatic-office.com/?p = 344简而言之,您希望录制宏并转到数据 - >从网络导航到您的网站并选择您想要的表格。

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 ;)

我过去使用了上面的解决方案“get element by id”类型的东西,它对于一些元素很有用,但是如果你想要一个完整的表,并且你没有超级经验,只需记录一个宏。不要告诉你的朋友,然后重新格式化它看起来像你自己的工作,所以没有人知道你使用宏工具;)

#1