How can we scrape web page data of drop down list options with VBA. Sometimes we need to know the options in a drop down list of a ‘select’ object in a form. Mostly our aim is to select the option of our choice from the drop down list options automatically and sometimes we may wish to know about the exact choices. In this training video we show how to find the number of options in a drop down list, get the options data into our worksheet and finally select an option of our choice automatically using VBA. Watch the training video before studying the complete macro VBA code given below:

Watch this video on YouTube.

The complete macro code:

Sub getOptionData()
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://www.niceedu.in/centrelist1.php”
‘Wait until IE has loaded the web page

Do While ie.readyState <> READYSTATE_COMPLETE

Application.StatusBar = “Loading Web page …”

DoEvents

Loop

‘The web page has now loaded completely

Set html = ie.document

‘We find the number of choices in our option drop down list

Dim drp As HTMLFormElement
Set drp = html.getElementById(“sfield”)
Dim x As Long
x = html.forms.Length
MsgBox x

‘we get the option values into our worksheet

For x = 0 To 3
Cells(x + 1, 1) = drp.Item(x).innerText
Next x

‘Now we select the option value of our choice

drp.selectedIndex = 2

‘ we free memory

Set ie = Nothing
Application.StatusBar = “”
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:

HTML Form Elements