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.


Download Practice Workbook

You can download the practice workbook from here.


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.

excel vba scrape table from 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.

Open Visual Basic Editor

  • 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 rightclick on it.
  • From the options, select Module.

Insert Module Window


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

Type VBA Code

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.

Save and Run VBA 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.

Final Output excel vba scrape table from website


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.

Mehedi Hasan Shimul

Mehedi Hasan Shimul

Hi! I am Mehedi Hasan Shimul. As I am an Engineer solving different problems with the help of Excel amuses me. I write Excel related different problem solving articles here. Hope it will help you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo