We can scrape web data using class names with VBA. “The class attribute specifies one or more classnames for an element. The class attribute is mostly used to point to a class in a style sheet. However, it can also be used by a JavaScript via the HTML DOM to make changes to HTML elements with a specified class.”
Watch the video below to see how you can extract data from a web-page using the class attribute:
Watch the video on YouTube.
Here’s the complete VBA (macro) code:
Sub useClassnames()
Dim element As IHTMLElement
Dim elements As IHTMLElementCollection
Dim ie As InternetExplorer
Dim html As HTMLDocument
‘open Internet Explorer in memory, and go to website
Set ie = New InternetExplorer
ie.Visible = True
ie.navigate “http://doctor.webmd.com/find-a-doctor/specialty/dermatology/alabama/gurley”
‘Wait until IE has loaded the web page
Do While ie.readyState <> READYSTATE_COMPLETE
Application.StatusBar = “Loading Web page …”
DoEvents
Loop
Set html = ie.document
Set elements = html.getElementsByClassName(“result”)
Dim count As Long
Dim erow As Long
count = 0
For Each element In elements
If element.className = “result” Then
erow = Sheet1.Cells(Rows.count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 1) = html.getElementsByTagName(“h2”)(count).innerText
Cells(erow, 2) = html.getElementsByClassName(“address”)(count).innerText
count = count + 1
End If
Next element
Range(“A2:B11”).Select
Columns(“A:A”).EntireColumn.AutoFit
Columns(“B:B”).ColumnWidth = 36
‘MsgBox count
End Sub
Important: Before you run the above code you must activate the following as shown in the video and in the image below:
- Microsoft HTML Library
- Microsoft Internet Controls
Further reading: