Query div element by class name using Excel VBA

Question

I'm trying to get the data to Excel of a div element with a specific class name, like:

<div class="myClass">
   <span>Text1</span>
   <span>Text2</span>
</div>

My VBA code:

Sub GetData()
Dim oHtml       As HTMLDocument
Dim oElement    As Object

Set oHtml = New HTMLDocument

With CreateObject("WINHTTP.WinHTTPRequest.5.1")
    .Open "GET", "http://www.example.com/", False
    .send
    oHtml.body.innerHTML = .responseText
End With

For Each oElement In oHtml.getElementsByClassName("myClass")
    Debug.Print oElement.Children(0).src
Next oElement
End Sub

This is returning the error: Run-time error: '438': Object doesn't support this property or method. The error is on line Debug.Print ...

I have activated the following refereces: Microsoft HTML Object Library Microsoft Internet Controls

I want to be able to select the text on the first or second span and paste it on a cell, how can I do this?

Answer 1

Here's how I've done it in the past:

Set oElement = oHtml.getElementsByClassName("myClass")
i = 0
While i < oElement.Length
    Debug.Print oElement(i).innerText
  i = i + 1
Wend

you might want innerHtml instead?

Answer 2

This worked for me:

Dim oHtml As HTMLDocument
Dim oElement As Object

Set oHtml = New HTMLDocument

With CreateObject("WINHTTP.WinHTTPRequest.5.1")
    .Open "GET", "http://www.example.com", False
    .send
    oHtml.body.innerHTML = .responseText
End With

Set dados = oHtml.getElementsByClassName("myClass")(0).getElementsByTagName("span")

i = 0
For Each oElement In dados
    Sheets("Sheet1").Range("A" & i + 1) = dados(i).innerText
    i = i + 1
Next oElement