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

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

5 thoughts 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

    Regards
    Gaurang

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

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

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

Leave a Reply

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