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
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?
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
Hello,
my problem is to extract specific data from website http://www.betexplorer.com/soccer/england/premier-league/teaminfo.php?team_id=KrrdAMyI.
I’d like to get into my excel document only the matches in tournament by extracting each single html element, is it possible by VBA code?
Could u help me out, pls?
Regards,
Carlo
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.
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
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
EDIT/ADD: I am using Windows10 and instead of Internet Explorer it is Edge Browser provided by Windows10 with Excel Ver.2010
Hello Master, I’m wrong with 438
(For t = 0 To elemCollection.Length-1)
The object does not accept this property or method- how to solve?