Extract Data from Multiple Tables in Web Page with VBA

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