Excel VBA: Get inner text of HTML table td
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
UPDATE
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
UPDATED CODE TO DEMONSTRATE FUNCTION ON 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
This works for me. I have set reference to Microsoft HTML Object Library
BTW you are missing a "
in <div id=myDiv">
Option Explicit
Sub Sample()
Dim objIe As Object, xobj As HTMLDivElement
Set objIe = CreateObject("InternetExplorer.Application")
objIe.Visible = True
objIe.navigate "C:\a.htm"
While (objIe.Busy Or objIe.READYSTATE <> 4): DoEvents: Wend
Set xobj = objIe.document.getElementById("myDiv")
Set xobj = xobj.getElementsByClassName("myTable").Item(0)
Set xobj = xobj.getElementsByClassName("data")(0)
Debug.Print xobj.innerText
Set xobj = Nothing
objIe.Quit
Set objIe = Nothing
End Sub
Screenshot: