Excel VBA:获取HTML表格td的内部文本 [英] Excel VBA: Get inner text of HTML table td
问题描述
我正在使用excel从网页获取值。除了其他元素,HTML包含下表:
I'm using excel to get values from a webpage. Among other elements, the HTML contains the following table:
<div id="myDiv">
<table class="myTable">
<tbody>
<tr>
<td>Text1:</td>
<td class="data"><strong>0.51</strong></td>
</tr>
<tr>
<td>Text2:</td>
<td class="data"><strong>2199</strong></td>
</tr>
</tbody>
</table>
</div>
页面存储在变量oHtml中。抓住桌子外面的其他元素是很好的。但是当我尝试捕获值0,51时,我在控制台中使用JS:
The page is stored in variable oHtml. It is working fine to grab other elements outside this table. But when I try to capture the value 0,51 for example, I use JS in the console:
document.getElementById("myDiv").getElementsByClassName("myTable")[0].getElementsByClassName("data")[0].innerText
选择值0,51。
但是,函数内使用的以下VBA代码返回#VALUE!
However, the following VBA code used inside a function is returning #VALUE!
Function myFunction(id)
Call myConnection(id)
Set myDadta = oHtml.getElementById("myDiv").getElementsByClassName("myTable")(0).getElementsByClassName("data")(0)
myFunction = myData.innerText
End Function
这是与IE的连接:
Public Sub myConnection(id)
Set oHtml = New HTMLDocument
With CreateObject("WINHTTP.WinHTTPRequest.5.1")
.Open "GET", "http://www.example.com" & id, False
.send
oHtml.body.innerHTML = .responseText
End With
End Sub
正如我所说,这种语法与这个表中的其他元素工作正常,所以为什么我会收到这个错误? >
As I said, this kind of syntax is working fine with other elements out of this table in this same page, so why am I getting this error?
推荐答案
你似乎在你的变量名中有一个错字。你有没有使用 Option Explicit
?
You seem to have a typo in your variable name. Have you used Option Explicit
?
Function myFunction(id)
Call myConnection(id)
Set myDadta = oHtml.getElementById("myDiv").getElementsByClassName("myTable")(0).getElementsByClassName("data")(0)
myFunction = myData.innerText ' <-- This line
End Function
更新
我把一个Button放在VBA表单上,并且修改了以下代码:
I put a Button on VBA form and the following corrected code that works:
Option Explicit
Dim oHtml, myData
Private Sub CommandButton1_Click()
MsgBox myFunction(0)
End Sub
Function myFunction(id)
Call myConnection(id)
Set myData = oHtml.getElementById("myDiv").getElementsByTagName("Table")(0).getElementsByTagName("td")(1)
myFunction = myData.innerText ' <-- will give 0.51
End Function
Public Sub myConnection(id)
Set oHtml = New HTMLDocument
With CreateObject("WINHTTP.WinHTTPRequest.5.1")
'.Open "GET", "http://www.example.com" & id, False
.Open "GET", "http://localhost/Test/Test.htm", False '<-- this is my local machine; replace appropriately
.send
oHtml.body.innerHTML = .responseText
End With
End Sub
更新的代码演示LIVE URL上的功能
Option Explicit
Dim oHtml, myData
Private Sub CommandButton1_Click()
MsgBox myFunction(0)
End Sub
Function myFunction(id)
Call myConnection(id)
Set myData = oHtml.getElementById("overallRatios").getElementsByTagName("Table")(0).getElementsByTagName("td")(1)
myFunction = myData.innerText
End Function
Public Sub myConnection(id)
Set oHtml = New HTMLDocument
With CreateObject("WINHTTP.WinHTTPRequest.5.1")
'.Open "GET", "http://www.example.com" & id, False
.Open "GET", "http://www.reuters.com/finance/stocks/overview?symbol=PTI.LS", False
.send
oHtml.body.innerHTML = .responseText
End With
End Sub
这篇关于Excel VBA:获取HTML表格td的内部文本的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!