Bringing table from website into spreadsheet

TMal830

New Member
Joined
Sep 24, 2013
Messages
12
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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

joeyjoejoejrshabidu

New Member
Joined
Nov 21, 2016
Messages
12
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

Active Member
Joined
Oct 1, 2015
Messages
433
Try this:

Code:
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,133,530
Messages
5,659,358
Members
418,498
Latest member
nattynat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top