Bringing table from website into spreadsheet

[TMal830]

Hello All,

I'm not new to VBA, but am very new to anything related to html or internet explorer. I'm trying to find a way to copy the tables at the following website to an excel sheet. All the examples I've seen online have been a little more complicated than mine. If anyone could help out with a simple code or a link to a website that already has the information, it would be greatly appreciated!

https://www.google.com/finance/option_chain?q=NASDAQ:SSYS&ei=FNRAWNitA9mOsQHFkI6YAQ

 

[joeyjoejoejrshabidu]

You could use the excel power query add-in for this. You'll need to install it first if you're using excel 2010 or 2013.

Power query > Get external data > From Web.

 

[Ombir]

Try this:

Sub Ombir_03Dec16()
Dim k       As Integer
Dim c       As Long
Dim i       As Long
Dim r       As Long
Dim trow    As Long
Dim ie      As InternetExplorer
Dim doc     As HTMLDocument
Dim tbl     As HTMLTable
Dim otable  As Variant
Dim tcell   As Object
Dim tblcoll As Object

Set ie = New InternetExplorer

With ie
    .Visible = True
    .Navigate "https://www.google.com/finance/option_chain?q=NASDAQ%3ASSYS&ei=FNRAWNitA9mOsQHFkI6YAQ"
    Do While .ReadyState <> 4: DoEvents: Loop
    Do Until .Busy: DoEvents: Loop
End With

Set doc = ie.Document
Set tblcoll = doc.getElementById("option-chain-stacked").getElementsByTagName("table")

r = 1
For Each tbl In tblcoll
i = 1
ReDim otable(0 To tbl.Rows.Length, 0 To tbl.Rows(1).Cells.Length)
    Do While trow < tbl.Rows.Length
        c = 1
        For Each tcell In tbl.Rows(trow).Cells
            If tcell.innerText <> "" Then
                otable(i, c) = tcell.innerText
                c = c + 1
            End If
        Next
        i = i + 1
        trow = trow + 1
    Loop
Range("A" & r).Resize(i, c) = otable
Range("A" & r) = doc.getElementsByClassName("gf-control")(k).getElementsByTagName("span")(0).innerText
trow = 0: r = i + 1: k = k + 1
Next
ie.Quit
End Sub

Before running the code, make sure to add reference to the two libraries given below in VBE Editor.

1. Microsoft Internet Controls
2. Microsoft HTML object Library


Regards,
Ombir