scrape example

Option Explicit

Sub WebScraping()

Dim oIE As Object        ' for Internet Explorer Object ...
Dim sURL As String        ' URL String ..
Dim sStocklist As String        ' Stocklist String ...
Dim vTxtInput As Variant        ' input texbox ...
Dim ieDoc As Object        ' the document retrieved ...
Dim IeTable As Object        ' the table where our data resides ...
Dim ieCell As Object        ' the cells (in the table above) holding our data ...

Dim ElementCol As MSHTML.IHTMLElementCollection        ' to loop thru element collection ...
Dim btnInput As MSHTML.HTMLInputElement        '           |
Dim x As Integer, i As Integer, p As Integer        ' misc counters

' load our stocklist
sStocklist = "MSFT,GE,O,DIS,C,JPM,BBT,VFINX,T,SAN,VZ"

' instantiate the oIE object ...
Set oIE = CreateObject("InternetExplorer.Application")

' open Yahoo finance ...
sURL = "http://www.Finance.Yahoo.com"
With oIE
    .Navigate sURL
    .Visible = True

    ' loop until the page finishes loading ...
    Do While .Busy
    Loop

    ' enter our stocklist in the stockqoutes input box
    Set vTxtInput = .document.getElementsByName("s")
    vTxtInput(0).Value = sStocklist

    ' click 'Submit' button
    Set ElementCol = .document.getElementsByTagName("button")
    For Each btnInput In ElementCol
        If btnInput.Title = "Get Quotes" Then
            btnInput.Click
            Exit For
        End If
    Next btnInput

    ' loop until the page finishes loading
    Do While .Busy
    Loop

    Sheets("Web Scraping Using Automation").Activate
    Range("Start1").Select

End With

Set ieDoc = oIE.document

'Loop through all the elements in the document via the 'all' property
For i = 0 To ieDoc.all.Length - 1
    ' check that we have the right table ...
    If TypeName(ieDoc.all(i)) = "HTMLTable" And _
       InStr(ieDoc.all(i).innerText, "Symbol") > 0 Then
        Set IeTable = ieDoc.all(i)
        ' loop thru each row in our table
        ' note we skip the first (header) row below ...
        For x = 1 To IeTable.Rows.Length - 1
            ' loop thru the cells in the row ...
            For p = 0 To IeTable.Rows(x).Cells.Length - 1
                Set ieCell = IeTable.Rows(x).Cells(p)
                ActiveCell.Offset(x, p).Value = ieCell.innerText
            Next
        Next
        Exit For
    End If
Next i

' clean up ...
oIE.Quit
Set oIE = Nothing

End Sub