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.

Table of Contents hide

Download Practice Workbook

Step-by-Step Procedures to Scrape Table from Website Using VBA in Excel

STEP 1: Open Visual Basic Editor

STEP 2: Insert Module Window

STEP 3: Type VBA Code

STEP 4: Save and Run VBA Code

Final Output

Conclusion

Download Practice Workbook

You can download the practice workbook from here.

Scrape Table from Website.xlsm

Step-by-Step Procedures to Scrape Table from Website Using VBA in Excel

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

VBA Code Explanation:

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

In this part, we created a subprocedure get_table_web and declared some variables.

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")

And this part gets the desired table from the website.

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

Again in this part, we declared some more variables to select the cell reference in our worksheet where the table should be included.

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

In this part, we are looping through the table headers.

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

At last, this part loops through the table cells and puts the data from the website table into our active worksheet.

Note:

urlc = “https://uk.investing.com/rates-bonds/financial-futures“: Here you should write the URL of the website from where you want the table.
Set tb = ig.document.getElementById(“cr1”): Here instead of “cr1” type the Table ID by inspecting the website table.
Set mys = ThisWorkbook.Sheets(“Financial_Futures”): Here “Financial_Futures” is the name of our active worksheet. You can change it according to your worksheet name.

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.

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 stepwise 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. Visit our ExcelDemy Website for similar articles regarding Excel.