use getelement to get class with several values

Question

I am using VBScript and getElementsByClassName to get data from HTML to Excel. Unfortunately, a site has changed their coding, so now I am unsure how to get the data in the class, since it is now divided into several parts.

The page source looks like this:

<span class="mod-tearsheet-recommendation__visual__column">
    <i data-recommendation-count="5" style="background-color:#458B00; height:25%"></i>
    <i data-recommendation-count="2" style="background-color:#74A840; height:10%"></i>
    <i data-recommendation-count="11" style="background-color:#777777; height:55%"></i>
    <i data-recommendation-count="2" style="background-color:#DF6060; height:10%"></i>
    <i data-recommendation-count="0" style="background-color:#CC0000; height:0%"></i>
</span>

I am interested only in the values 5, 2, 11, 2, 0.

http://markets.ft.com/data/equities/tearsheet/forecasts?s=MMM:NYQ

I use getElementByClassname like this:

ws.Range("V2").Value = objExplorer.document.getElementsByClassName("mod-tearsheet-recommendation__visual__column")(1).innerHtml

But this doesn't separate the values in the class.

Is there a way to get each "i data-recommendation-count"-value in the class?

Answer 1

getElementsByClassName("mod-tearsheet-recommendation__visual__column") is returning a collection of span elements which are items of the collection. Each of the span's has 5 childNodes. Each item in the childNodes collection has a data-recommendation-count attribute.

<span class="mod-tearsheet-recommendation__visual__column">
    <i data-recommendation-count="5" style="background-color:#458B00; height:25%"></i>
    <i data-recommendation-count="2" style="background-color:#74A840; height:10%"></i>
    <i data-recommendation-count="11" style="background-color:#777777; height:55%"></i>
    <i data-recommendation-count="2" style="background-color:#DF6060; height:10%"></i>
    <i data-recommendation-count="0" style="background-color:#CC0000; height:0%"></i>
</span>

I show you three ways to reference the data-recommendation-count values in my code below. The way that I located these values was to set a break point after each reference and then drilled down into the reference's properties in the locals window. Next I would try test these properties in the imediate window.

Sub SearchSite()
    Dim i As Integer, j As Integer
    Dim tearSheetsTags, tearsheet, dataCount
    Dim objIE As InternetExplorer
    Set objIE = New InternetExplorer

    objIE.Visible = True
    objIE.navigate "http://markets.ft.com/data/equities/tearsheet/forecasts?s=MMM:NYQ"

    Do While objIE.Busy = True Or objIE.readyState <> 4
        DoEvents
    Loop

    '   <span class="mod-tearsheet-recommendation__visual__column">
    '       <i data-recommendation-count="5" style="background-color:#458B00; height:25%"></i>
    '       <i data-recommendation-count="2" style="background-color:#74A840; height:10%"></i>
    '       <i data-recommendation-count="11" style="background-color:#777777; height:55%"></i>
    '       <i data-recommendation-count="2" style="background-color:#DF6060; height:10%"></i>
    '       <i data-recommendation-count="0" style="background-color:#CC0000; height:0%"></i>
    '   </span>

    Set tearSheetsTags = objIE.document.getElementsByClassName("mod-tearsheet-recommendation__visual__column")

    For Each tearsheet In tearSheetsTags
        i = i + 1
        j = 0
        For Each dataCount In tearsheet.ChildNodes

            j = j + 1
            Cells(i, j) = dataCount.getAttribute("data-recommendation-count")
        Next
    Next

    With tearSheetsTags
        For i = 0 To .Length - 1
            For j = 0 To .Item(i).ChildNodes.Length - 1
                Cells(i + 7, j + 1) = .Item(i).ChildNodes.Item(j).getAttribute("data-recommendation-count")
            Next j
        Next i
    End With

    With objIE.document.getElementsByClassName("mod-tearsheet-recommendation__visual__column")
        With .Item(0)
            Cells(13, 1) = .ChildNodes.Item(0).getAttribute("data-recommendation-count")
            Cells(13, 2) = .ChildNodes.Item(1).getAttribute("data-recommendation-count")
            Cells(13, 3) = .ChildNodes.Item(2).getAttribute("data-recommendation-count")
            Cells(13, 4) = .ChildNodes.Item(3).getAttribute("data-recommendation-count")
            Cells(13, 5) = .ChildNodes.Item(4).getAttribute("data-recommendation-count")
        End With
        With .Item(1)
            Cells(14, 1) = .ChildNodes.Item(0).getAttribute("data-recommendation-count")
            Cells(14, 2) = .ChildNodes.Item(1).getAttribute("data-recommendation-count")
            Cells(14, 3) = .ChildNodes.Item(2).getAttribute("data-recommendation-count")
            Cells(14, 4) = .ChildNodes.Item(3).getAttribute("data-recommendation-count")
            Cells(14, 5) = .ChildNodes.Item(4).getAttribute("data-recommendation-count")
        End With
        With .Item(2)
            Cells(15, 1) = .ChildNodes.Item(0).getAttribute("data-recommendation-count")
            Cells(15, 2) = .ChildNodes.Item(1).getAttribute("data-recommendation-count")
            Cells(15, 3) = .ChildNodes.Item(2).getAttribute("data-recommendation-count")
            Cells(15, 4) = .ChildNodes.Item(3).getAttribute("data-recommendation-count")
            Cells(15, 5) = .ChildNodes.Item(4).getAttribute("data-recommendation-count")
        End With
        With .Item(3)
            Cells(16, 1) = .ChildNodes.Item(0).getAttribute("data-recommendation-count")
            Cells(16, 2) = .ChildNodes.Item(1).getAttribute("data-recommendation-count")
            Cells(16, 3) = .ChildNodes.Item(2).getAttribute("data-recommendation-count")
            Cells(16, 4) = .ChildNodes.Item(3).getAttribute("data-recommendation-count")
            Cells(16, 5) = .ChildNodes.Item(4).getAttribute("data-recommendation-count")
        End With
        With .Item(4)
            Cells(17, 1) = .ChildNodes.Item(0).getAttribute("data-recommendation-count")
            Cells(17, 2) = .ChildNodes.Item(1).getAttribute("data-recommendation-count")
            Cells(17, 3) = .ChildNodes.Item(2).getAttribute("data-recommendation-count")
            Cells(17, 4) = .ChildNodes.Item(3).getAttribute("data-recommendation-count")
            Cells(17, 5) = .ChildNodes.Item(4).getAttribute("data-recommendation-count")
        End With

    End With

    objIE.Quit

End Sub