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.
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.
Read More: How to Import Table from Website to Excel (2 Easy Ways)
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.
Read More: How to Use Web Scraping with Excel VBA (3 Suitable Examples)
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"
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.
Read More: How to Scrape Data from a Website into Excel (2 Easy Methods)
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.
Read More: Automated Data Scraping from Websites into Excel
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.