How to extract data from multiple tables in web page with VBA. We had learnt how to scrape data from tables in web pages earlier. Now many people were not able to understand some of the details. We have modified the code and created a video to explain the details step by step. Watch the video below before you study the VBA code to scrape data from tables automatically:
Watch this video on YouTube.
The complete VBA code to get data from tables in a web page is self explanatory:
Sub extractTablesData()
‘we define the essential variables
Dim IE As Object
‘Dim obj as Object
Dim r As Long, c As Long, t As Long
Dim elemCollection As Object
Dim eRow As Long
‘add the “Microsoft Internet Controls” reference in your VBA Project indirectly
Set IE = CreateObject(“InternetExplorer.Application”)
With IE
.Visible = True
.navigate (“http://www.bankofcanada.ca/rates/exchange/daily-closing-past-five-day/”)
‘.navigate (“http://www.exceltrainingvideos.com/test/tables.html”)
‘we ensure that the web page downloads completely before we fill the form automatically
While IE.ReadyState <> 4
DoEvents
Wend
‘Clearing any unnecessary or old data in Sheet1
ThisWorkbook.Sheets(“Sheet1”).Range(“A2:K500”).ClearContents
Set elemCollection = IE.Document.getElementsByTagName(“TABLE”)
Range(“A1”) = “Table 1”
For t = 0 To (elemCollection.Length – 1)
For r = 0 To (elemCollection(t).Rows.Length – 1)
eRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
For c = 0 To (elemCollection(t).Rows(r).Cells.Length – 1)
ThisWorkbook.Worksheets(1).Cells(eRow, c + 1) = elemCollection(t).Rows(r).Cells(c).innerText
Next c
Next r
Cells(eRow + 1, 1) = “Table ” & t + 2
If t = elemCollection.Length – 1 Then
Cells(eRow + 1, 1) = “”
End If
Next t
End With
Range(“A1:K500”).Columns.AutoFit
‘cleaning up memory
Set IE = Nothing
End Sub
Sir
How to send reminder mailers which includes name of sender and which project is due. In your case it is credit card name.
for e.g i want to specify below
Dear “(Name)”
Your “project name” is due
Regards
Gaurang
I am getting an Error: Object doesn’t support this property or method
Sub extractTablesData()
‘define varialbes
Dim IE As Object
Dim obj As Object
Dim r As Long, c As Long, t As Long
Dim elemCollection As Object
Dim eRow As Long
‘add the microsoft internet controls
Set IE = CreateObject(“InternetExplorer.Application”)
With IE
.Visible = True
‘.navigate (“http://www.bankofcanada.ca/rates/exchange/daily-exchange-rates/”)
.navigate (“http://www.exceltrainingvideos.com/test/tables.html”)
‘we ensure that the webpage is downloaded completely
While IE.ReadyState 4
DoEvents
Wend
ThisWorkbook.Sheets(“Sheet1”).Range(“A2:K500”).ClearContents
Set elemCollection = IE.Document.getElemntsByTagName(“TABLE”)
For t = 0 To (elemCollection.Length – 1)
For r = 0 To (elemCollection(t).Rows.Length – 1)
eRow = Sheet1.Cells(Rows.Count, 1).End(x1Up).Offset(1, 0).Row
For c = 0 To (elemCollection(t).Rows(r).Cells.Length – 1)
ThisWorkbook.Worksheets(1).Cells(eRow, c + 1) = elemCollection(t).Rows(r).Cells(c).innerText
Next c
Next r
Cells(eRow + 1, 1) = “Table” & t + 2
If t = elemCollection.Length – 1 Then
Cells(eRow + 1, 1) = “”
End If
Next t
End With
Range(“A1:K500”).Columns.AutoFit
‘clean up the memory
Set IE = Nothing
End Sub
Check your code again. Did you copy and paste?
Hi Sir,
I have an order form with multiple drop down list for multiple items’ catalogs
I would like to know is there the way to link these items on the search on a specific website like amazon to place the order and it can pull data from the order like quantity we placed and price to excel cells do that we can keep track all our orders in the excel.
Thank you
Hello Dinesh,
Thanks for your post “Extract Data from Multiple Tables in Web Page with VBA”. I found it very interesting.
I am trying to get all tables from that website: https://www.iaaf.org/records/all-time-toplists/sprints/100-metres/outdoor/men/senior?regionType=world&timing=all&windReading=all&page=1&bestResultsOnly=false&firstDay=1900-01-01&lastDay=2018-05-01
Would you be so kind to give me a hand in order to be able to list the content of the 259 tables?
Many thanks in advance.
Carlos.
thank u admin! thanks for sharing useful information.
Hello,
I found your video very useful, thank you.
I wonder if you would have the time to show me (and others) how to extract data from the following table please, on link https://www.oanda.com/forex-trading/analysis/financing-calculator
Wish to complete the web table from VBA, using “Number of Units” 100000, “Hours Held” 24, and then for each currency pair in the long list extract the “Interest Earned (USD)” for both Buy/Long and Sell/Short trades.
Any help would be greatly appreciated.
Thank you
Sir
Thank you for this wonderful video. Your explanation is exemplary.
Please if you can add data from nested tables
Thank you
Nice tutorial!
Is there a way to select table? I got 9 and i just want 2 and 4.