VBA Web scraping GetElementsByClassName

[Pak Mariman]

I am trying to extract an element from a website and stick it into a cell on my spreadsheet:

Worksheets("Sheet1").Cells(1, 1).Value = IE.document.getElementsByClassName("apples").innerText

(in this I have dimmed the variable IE as New InternetExplorer). The navigate bit works.

The website bit that I am after looks like:

<span id="apples">here is the text I am after<br>and this text too<br></span>

I get a 438 error.

How and why, and how to change my code? I cannot use the following code:

Worksheets("Sheet1").Cells(1, 1).Value = IE.document.getElementsByTagName("span")(counter).innerText

because I am probing several similar URLs and the number of the "span" tag is not consistent.

Thanks!

 

[Pak Mariman]

Ah... Added info: I did tick the references Microsoft HTML Object Library and the Microsoft Internet Controls.

 

[Pak Mariman]

Bump

 

[John_w]

getElementsByClassName returns an array and to reference the first array element you would use getElementsByClassName("apples")(0).innerText

However, your HTML shows that "apples" is an id, not a class name, so you should use:

Worksheets("Sheet1").Cells(1, 1).Value = IE.document.getElementById("apples").innerText

 

[Pak Mariman]

getElementsByClassName returns an array and to reference the first array element you would use getElementsByClassName("apples")(0).innerText

However, your HTML shows that "apples" is an id, not a class name, so you should use:

Worksheets("Sheet1").Cells(1, 1).Value = IE.document.getElementById("apples").innerText

Thanks John!

I used the getElementByID as you suggested. It now gives me a 424 error.

 

[John_w]

The error indicates that an element with id "apples" doesn't (yet) exist, either because the page is not completely loaded or the element is in a frame.

To prove the former, click Debug on the error message and press F8 to execute the yellow highlighed line again. If no error occurs it means that the page wasn't loaded when that line was first executed.

to fix this add a wait loop after the IE.navigate:

    While IE.Busy Or IE.readyState <> 4: DoEvents: Wend

And then get the span element using this code:

    Dim HTMLdoc As HTMLDocument
    Dim span As HTMLSpanElement
    Set HTMLdoc = IE.document
    Do
        Set span = HTMLdoc.getElementById("apples")
        DoEvents
    Loop While span Is Nothing
    Worksheets("Sheet1").Cells(1, 1).Value = span.innerText