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

Answer 1

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