Excel VBA to Scrape Table from Website (With Easy Steps)

Sometimes, we may want to work in Excel with a data table from a specific website. It’s quite hazardous to input the table from the website to Excel manually. But there’s an easy way. We can use VBA code to scrape a table from any website to Excel worksheet. Here, I will show step-by-step procedures to scrape a table from a website using VBA code in Excel.

Excel VBA to Scrape Table from Website (With Easy Steps)

Scraping a table from a website is a relatively easy task if we use the VBA code of Excel. For demonstration purposes, I will include a real-time data table from the investing.com website.

STEP 1: Open Visual Basic Editor

As the initial step, we have to open the Visual Basics for Applications window. Follow the given procedures for that.

• Firstly, go to the Developer tab and select Visual Basic to open the VBA.
• Also, you can press Alt + F11 to open the VBA.

• Afterward, click on Tools and select References from the VBA window toolbar.

• From the References – VBAProject window, select Microsoft HTML Object Library & Microsoft Internet Controls.
• Then, press OK.

STEP 2: Insert Module Window

Now, we need to open the code window. For that follow the given procedures.

• From VBA Projects select the active sheet and right–click on it.
• From the options, select Module.

STEP 3: Type VBA Code

It’s time to write the VBA code. You can write 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 VBA Code

Finally, you need to run the code.

• First, click on the Save button on the VBA window.
• Then, click on the Run button on the VBA window.
• Alternatively, you can press the F5 key to run the code.

Final Output

After running the code, come back to the active worksheet and we will see the desired table from the website is included in our worksheet.

Download Practice Workbook

You can download the practice workbook from here.

Conclusion

For different analyses, we may want a table from a website. It’s a bit tough and time-consuming to include the table manually. Here, I have shown the step-by-step procedures to use the VBA code of Excel to scrape a table from a website. I hope it will help you. Lastly, if you have any suggestions or queries, leave a comment in the comment section.