Issue:
I would like to retrieve a particular value (Prev Close) and (Div & Yield) from multiple internet explorer websites and copy them to multiple cells (Column C and D) automatically. 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)
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&typ...ctrl1&uhb=uhb2
Below is my VBA code:
Private Sub CommandButton1_Click()
Dim ie As Object
Dim prevClose As String
Dim div As String
Set ie = CreateObject("InternetExplorer.Application")
ie.Visible = 0
For r = 2 To 10
ie.navigate "http://finance.yahoo.com/q;_ylt=AsqtxVZ0vjCPfBnINCrCWlXJgfME?uhb=uhb2&fr=uh3_finance_vert_gs_ctrl1_e&type=2button&s=" & Cells(r, "B").Value
Do
DoEvents
Loop Until ie.readyState = 4
Dim Doc As HTMLDocument
Set Doc = ie.document
prevClose = Trim(Doc.getElementById("table1").getElementsByTagName("td")(0).innerText)
Cells(r, "C").Value = prevClose
div = Trim(Doc.getElementById("table2").getElementsByTagName("tr")(7).getElementsByTagName("td")(0).innerText)
Cells(r, "D").Value = div
Next r
End Sub
Cheers
Bookmarks