CODEWITHSUNDEEP
vbaexcel
Matteo NNZ
Matteo NNZ

Reputation: 12685

Iterate through an object HTML TableRowElement to get the innerText of one of its children

I have written the following script that, from this webpage, should be able to extract the values contained into the table field "Chg. %".

Sub Test()

Dim getIE As Object, appIE As Object, indexValues As Object
Set getIE = CreateObject("InternetExplorer.application")
Set appIE = getIE

With appIE
.Navigate "http://www.investing.com/indices/world-indices"
.Visible = True
End With

Application.Wait TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 10)

Set indexValues = appIE.document.getElementById("pair_13376")

End Sub

The variable indexValues is a [object HTMLTableRowElement]; I guess, according to the structure of the webpage, it is containing the value "-0.13%" that I need in the 8th td.innerText.

enter image description here

How can I extract this value? I have tried something like:

i = 0
For Each td In indexValues
    i = i + 1
    If i = 8 Then
        myValue = td.innerText
    End If
Next

However, this method fails because the object indexValues is not an iterable list of objects. Can anyone help please? NOTE: the index I'm referring to is the very first one of the webpage I've posted, i.e. "Merval"

Upvotes: 0

Views: 1713

Answers (2)

Sathish Kothandam
Sathish Kothandam

Reputation: 1520

Run this code .. Is this what you wanted .. let me know if you need more help :) *Tested*

    Sub Test()

Dim getIE As Object, appIE As Object, indexValues As Object
Set getIE = CreateObject("InternetExplorer.application")
Set appIE = getIE

With appIE
.Navigate "http://www.investing.com/indices/world-indices"
.Visible = True
End With

Application.Wait TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 10)

 For Each tbl In getIE.document.getElementsByTagName("TABLE")
    '    tabno = tabno + 1
     '   If tabno <> 5 Then GoTo NxtTbl
            For Each rw In tbl.Rows
                nextrow = nextrow + 1
                Set Rng = Range("A" & nextrow)
                Cofs = 0
                I = 0
                For Each Cl In rw.Cells
                    I = I + 1
                    If I = Int(I / 3) * 3 Then GoTo NxtCl
                        Rng.Offset(, Cofs).Value = Cl.outerText
                        Cofs = Cofs + 1
NxtCl:
                 Next Cl
             Next rw
NxtTbl:
      Next tbl

      Range("C:E").Delete

End Sub

Upvotes: 0

ron
ron

Reputation: 1378

The following line will give you what you want. Change the index number if you want a different value on that row

     myValue = appIE.Document.getElementById("pair_13376").getElementsByTagName("td")(7).innertext

Upvotes: 1

Related Questions