Scraping Web Page Tables Data Using VBA

I was watching this video of yours

‘Automated Data Scraping from Websites into Excel http://www.youtube.com/watch?v=qbOdUaf4yfI’

and I was wondering if it was possible to scrape the first name, last name, organization, certification, city, state, and zip code but it’s hard to get because they make it so you have to fill out a form to contact them via email. FROM THIS WEBSITE WWW.NAMB.ORG
If you use the Find A Mortgage Professional on the right hand side of the page and select ALL, it will show all the mortgage brokers in the database.
Best Regards,
Charlie G
Happy Holidays

Here’s the complete Excel VBA code to scrape data in tables of web pages using a ‘for next’ nested loop..

Sub extractTablesData()
‘we define the essential variables

Dim IE As Object, obj As Object
Dim myState As String
Dim r As Integer, c As Integer, t As Integer
Dim elemCollection As Object

‘add the “Microsoft Internet Controls” reference in your VBA Project indirectly
Set IE = CreateObject(“InternetExplorer.Application”)

‘more variables for the inputboxes – makes our automation program user friendly

myState = InputBox(“Enter the city where you wish to work”)

With IE

.Visible = True
.navigate (“https://www.namb.org”)

‘ we ensure that the web page downloads completely before we fill the form automatically
While IE.ReadyState <> 4
DoEvents
Wend

‘accessing the ListBox wit States data
For Each obj In IE.Document.All.Item(“csSB_Search_State”).Options

If obj.innerText = myState Then

obj.Selected = True

End If

Next obj

‘ accessing the button

IE.Document.getElementsByName(“Search”).Item.Click

‘ again ensuring that the web page loads completely before we start scraping data
Do While IE.busy: DoEvents: Loop

‘Clearing any unnecessary or old data in Sheet1
ThisWorkbook.Sheets(“Sheet1”).Range(“A1:K500”).ClearContents

Set elemCollection = IE.Document.getElementsByTagName(“TABLE”)

For t = 0 To (elemCollection.Length – 1)

For r = 0 To (elemCollection(t).Rows.Length – 1)
For c = 0 To (elemCollection(t).Rows(r).Cells.Length – 1)
ThisWorkbook.Worksheets(1).Cells(r + 1, c + 1) = elemCollection(t).Rows(r).Cells(c).innerText
Next c
Next r
Next t

End With

‘ cleaning up memory
Set IE = Nothing

End Sub

Watch the training video below:


Further reading:
Excel VBA Lesson 5: Looping

7 thoughts on “Scraping Web Page Tables Data Using VBA

  1. kazbear

    This has been great. Learned a lot.

    For my needs, the page I am navigating to has a Frame and the table information is contained within the source link for that frame. Any ideas on how I can access the information from the source link?

    Reply
  2. craigZhi

    I was trying something along the lines where we had to download the data and it has a few check boxes which need to be selected .How do i go about this.

    1.urx <- x
    2.Select a few check boxes
    3.Run "update button" on the same page <-x
    4.Now extract the table data

    Reply
  3. Nigel

    Is it possible to loop through a table and get the hyperlinks at the same time? Sometimes I need to get the text that is displayed when you hover over a link.

    Reply
  4. manoj

    I am pulling the data which is in table format from a website based on from date, I am giving the date by using input box so that it goes and sit in the form for from date box which is in website and search for results but not unfortunately the date which i am giving is not sitting in from date box, I am getting the blank results. Could you please help on this? thank You in advance.

    Option Explicit
    Sub extractTablesData()

    Dim IE As Object, obj As Object
    Dim myfromdate As String
    Dim r As Integer, c As Integer, t As Integer
    Dim elemCollection As Object

    Set IE = CreateObject(“InternetExplorer.Application”)

    myfromdate = InputBox(“Enter From date format YYYY-MM-DD”)

    With IE

    .Visible = True
    .navigate (“https://health-products.canada.ca/noc-ac/index-eng.jsp”)

    While IE.ReadyState 4
    DoEvents
    Wend

    For Each obj In IE.Document.getElementsByName(“nocFromdate”)

    If obj.innerText = myfromdate Then

    obj.Selected = True

    End If

    Next obj

    IE.Document.getElementsByName(“action”).Item.Click

    Do While IE.busy: DoEvents: Loop

    worksheets(“Sheet1”).Range(“A:Z”).ClearContents

    Set elemCollection = IE.Document.getElementsByTagName(“TABLE”)

    For t = 0 To (elemCollection.Length)

    For r = 0 To (elemCollection(t).Rows.Length)
    For c = 0 To (elemCollection(t).Rows(r).Cells.Length)
    worksheets(“Sheet1”).Cells(r + 1, c + 1) = elemCollection(t).Rows(r).Cells(c).innerText
    Next c
    Next r
    Next t

    End With

    Set IE = Nothing

    End Sub

    Reply
  5. B. Bhanushali

    Sir, Could you guide me if it is possible to download the following websites table in excel thru macro:
    https://www.nseindia.com/live_market/dynaContent/live_watch/equities_stock_watch.htm?cat=N

    I have tried web query and it does not return any data. I tried doing thru the scrap web method described by you but I have failed to extract any data. Any help or direction would be very helpful and appreciated.
    With Gratitudes and regards,
    B. Bhanushali

    Reply

Leave a Reply

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