Using Excel VBA to Scrape a Table from a Website – 4 Steps

Here is a real-time data table from the investing.com website. Here’s how we have extracted it.

STEP 1 – Open the Visual Basic Editor

• Go to the Developer tab and select Visual Basic to open the VBA window. You can press Alt + F11 to open the VBA window.

• Click Tools and select References.

• In the References – VBAProject window, check Microsoft HTML Object Library and Microsoft Internet Controls.
• Click OK.

STEP 2 – Insert a Module

• In VBA Projects, select the active sheet and right–click it.
• Select Module.

STEP 3 – Enter the VBA Code

Use the following code in the Module window.

Option Explicit
Sub get_table_web()
Dim ig As Object
Dim urlc As String
urlc = "https://uk.investing.com/rates-bonds/financial-futures"
Set ig = CreateObject("InternetExplorer.Application")
ig.Visible = True
ig.navigate urlc
Do While ig.busy: DoEvents: Loop
Do Until ig.readyState = 4: DoEvents: Loop
Dim tb As HTMLTable
Set tb = ig.document.getElementById("cr1")
Dim rowcounter As Integer
Dim columncounter As Integer
rowcounter = 4
columncounter = 2
Dim tro As HTMLTableRow
Dim tdc As HTMLTableCell
Dim thu
Dim mys As Worksheet
Set mys = ThisWorkbook.Sheets("Financial_Futures")
For Each tro In tb.getElementsByTagName("tr")
For Each thu In tro.getElementsByTagName("th")
mys.Cells(rowcounter, columncounter).Value = thu.innerText
columncounter = columncounter + 1
Next thu
For Each tdc In tro.getElementsByTagName("td")
    mys.Cells(rowcounter, columncounter).Value = tdc.innerText
columncounter = columncounter + 1
Next tdc
columncounter = 1
rowcounter = rowcounter + 1
Next tro
End Sub

Sub get_table_web()
Dim ig As Object
Dim urlc As String
urlc = "https://uk.investing.com/rates-bonds/financial-futures"

Set ig = CreateObject("InternetExplorer.Application")
ig.Visible = True
ig.navigate urlc
Do While ig.busy: DoEvents: Loop
Do Until ig.readyState = 4: DoEvents: Loop
Dim tb As HTMLTable
Set tb = ig.document.getElementById("cr1")

Dim rowcounter As Integer
Dim columncounter As Integer
rowcounter = 4
columncounter = 2
Dim tro As HTMLTableRow
Dim tdc As HTMLTableCell
Dim thu

Dim mys As Worksheet
Set mys = ThisWorkbook.Sheets("Financial_Futures")
For Each tro In tb.getElementsByTagName("tr")
For Each thu In tro.getElementsByTagName("th")
mys.Cells(rowcounter, columncounter).Value = thu.innerText
columncounter = columncounter + 1
Next thu

For Each tdc In tro.getElementsByTagName("td")
    mys.Cells(rowcounter, columncounter).Value = tdc.innerText
columncounter = columncounter + 1
Next tdc
columncounter = 1
rowcounter = rowcounter + 1
Next tro

STEP 4 – Save and Run the VBA Code

• Click Save in the VBA window.
• Click Run or press F5 to run the code.

Final Output

Go back to the active worksheet.

Download the Practice Workbook