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

‘Clearing any unnecessary or old data in Sheet1

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

‘cleaning up memory
Set IE = Nothing

End Sub

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

One thought on “Extract Data from Multiple Tables in Web Page with VBA”

  1. 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


Leave a Reply

Your email address will not be published. Required fields are marked *