Vba- retrieve value from multiple internet explorer websites to multiple cells

Question

Issue:

I would like to retrieve a particular value (Prev Close) from multiple internet explorer websites and copy them to multiple cells (Column C) automatically. I know how to retrieve value from a single internet explorer websites to a single cell. But i have no idea how to retrieve from multiple websites and copy them to multiple cells.

My computer info:

1.window 8.1

2.excel 2013

3.ie 11

My excel reference

Microsoft Object Library: yes

Microsoft Internet Controls: yes

Microsoft Form 2.0 Object library: yes

Microsoft Script Control 1.0: yes

URL:

http://finance.yahoo.com/q?s=hpq&type=2button&fr=uh3_finance_web_gs_ctrl1&uhb=uhb2

Below is my VBA code:

Private Sub CommandButton1_Click()

Dim ie As Object
Dim Doc As HTMLDocument
Dim prevClose As String


Set ie = CreateObject("InternetExplorer.Application")

    ie.Visible = 0

    ie.navigate "http://finance.yahoo.com/q;_ylt=AsqtxVZ0vjCPfBnINCrCWlXJgfME?uhb=uhb2&fr=uh3_finance_vert_gs_ctrl1_e&type=2button&s=" & Range("b2").Value
    Do
    DoEvents
    Loop Until ie.readyState = 4



Set Doc = ie.document


prevClose = Trim(Doc.getElementById("table1").getElementsByTagName("td")(0).innerText)
Range("c2").Value = prevClose


End Sub

Do you need to use multiple tabs or are you just getting it from multiple websites? — cheezsteak, Nov 12 '14 at 17:13

score 0 · Accepted Answer · answered Nov 12 '14 at 17:37

Don't use multiple tabs unless you really need to. It's an un-scalable solution that breaks quickly as the tabs add up.

It's far simpler and easier to just use one tab and deal with one webpage at a time using simple looping constructs. For this I am assuming that your URLs are the one your provided + some string contained in column B.

Private Sub CommandButton1_Click()
    Const YAHOO_PARTIAL_URL As String = "http://finance.yahoo.com/q;_ylt=AsqtxVZ0vjCPfBnINCrCWlXJgfME?uhb=uhb2&fr=uh3_finance_vert_gs_ctrl1_e&type=2button&s="

    Dim ie As Object
    Set ie = CreateObject("InternetExplorer.Application")
    ie.Visible = 0

    For r = 2 To 10 ' Or whatever your row count is.
        ie.navigate YAHOO_PARTIAL_URL & Cells(r, "B").Value
        Do
            DoEvents
        Loop Until ie.readyState = 4

        Dim Doc As HTMLDocument
        Set Doc = ie.document

        Dim prevClose As String
        prevClose = Trim(Doc.getElementById("table1").getElementsByTagName("td")(0).innerText)
        Cells(r, "C").Value = prevClose
    Next r

End Sub

I tried your code. it sometimes able to retrieve 1 row. sometimes 2, even 3 rows. And then it will show run time error 91. I then debug it. it happens on this line of code: prevClose = Trim(Doc.getElementById("table1").getElementsByTagName("td")(0).innerText) — pexpex223, Nov 12 '14 at 20:05
That means that the element did not exist when the code tried to access it. There are two possibilities. 1) that element doesn't exist on that webpage or 2) the code is trying to access it before it has loaded. I'm 99% its the latter. — cheezsteak, Nov 12 '14 at 20:48
@pexpex223 see [this answer](http://stackoverflow.com/questions/25749333/internet-explorer-fill-input-box-with-text-mask-using-vba/25753120#25753120) for waiting for elements to load before accessing them. — cheezsteak, Nov 12 '14 at 20:54

Vba- retrieve value from multiple internet explorer websites to multiple cells

1 Answers1