Excel VBA: Get inner text of HTML table td

Question

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>

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

And the value 0,51 is selected.

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

This is the connection to 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?

Answer 1

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:

Answer 2

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