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.