Scraping Webpage Tables Data Using VBA

Home More Excel (VBA) Training Videos
     Webhosting by Godaddy     MS Excel Books





I was watching this video of yours

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 yo 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:

Scraping Webpage Tables Data Using VBA

Watch the video on YouTube

Reference

     Webhosting by Godaddy     MS Excel Books
Home More Excel (VBA) Training Videos