Excel 2010 VBA - Pull Table from website

Question

I'm very new to Excel VBA and I was given some code to play with. I successfully modified it once, but then I tried to modify it again and it won't pull the right info. It might be because I don't know the table id for sure? I don't understand all of the code from this example...that's probably the other issue. Anyways I'm trying to pull the Historical Prices Table from this page. It pulls some data, but not the correct data. Any help would be appreciated. Thanks!

Here is my current code:

Sub GrabHistData()

    Dim Ptrtbl As Long, r As Long, c As Long
    Dim htm As Object
    Dim elemCollection As Object

    Set htm = CreateObject("htmlFile")

    With CreateObject("msxml2.xmlhttp")
        .Open "GET", "http://finance.yahoo.com/q/hp?s=TWTR&a=04&b=30&c=2012&d=01&e=7&f=2014&g=d", False
        .send
        htm.body.innerhtml = .responsetext
    End With

    Set elemCollection = htm.getElementsByTagName("TABLE")

    Ptrtbl = 1
    For Each elem In elemCollection
        Ptrtbl = Ptrtbl + 1
        If elem.ID <> "yfncsumtab" Then GoTo Nxtelem
        With elemCollection(Ptrtbl)

                For c = 0 To (.Rows(r).Cells.Length - 1)
                    Cells(r + 1, c + 1) = .Rows(r).Cells(c).innertext
                Next c

        End With
        Exit For
Nxtelem:
    Next elem

End Sub

Would [THIS](http://stackoverflow.com/questions/8798260/html-parsing-of-cricinfo-scorecards) help? — Siddharth Rout, Feb 07 '14 at 20:52
There's a "download to CSV" link on that page, sao you could instead just open the file directly in excel using `http://ichart.finance.yahoo.com/table.csv?s=TWTR&a=04&b=30&c=2012&d=01&e=7&f=2014&g=d&ignore=.csv` as the path (which is more or less the same pattern as the URL you're trying to scrape from...) — Tim Williams, Feb 07 '14 at 21:20
Ahh, good ideas. I really like the firefox element searching options. I also see now that the csv file method could be much easier. Thanks! — ZubaZ, Feb 07 '14 at 23:00

score 0 · Accepted Answer · answered Feb 08 '14 at 02:01

If you want to stick with your current approach, this works for me...

Sub GrabHistData()
    Dim Ptrtbl As Long, r As Long, c As Long
    Dim htm As Object
    Dim elemCollection As Object

    Set htm = CreateObject("htmlFile")

    With CreateObject("msxml2.xmlhttp")
        .Open "GET", "http://finance.yahoo.com/q/hp?s=TWTR&a=04&b=30&c=2012&d=01&e=7&f=2014&g=d", False
        .send
        htm.body.innerhtml = .responseText
    End With

    Set elemCollection = htm.getElementsByTagName("td")
    For Each itm In elemCollection
        If itm.classname = "yfnc_tabledata1" Then
            ActiveCell = itm.innertext

            If ActiveCell.Column = 7 Then
                ActiveCell.Offset(1, -6).Select
            Else
                ActiveCell.Offset(0, 1).Select
            End If
        End If
    Next
End Sub

Excel 2010 VBA - Pull Table from website

1 Answers1