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:

1. Microsoft HTML Library
2. Microsoft Internet Controls

Further reading:

Class Attribute in HTML

IHTMLELEMENT