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

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 ("")

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

'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

 ' 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

 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


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