Scraping Web Page Data of Drop Down List Options with VBA

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
References for Internet Explorer Automation

References for Internet Explorer Automation

Further reading:

HTML Form Elements

2 thoughts on “Scraping Web Page Data of Drop Down List Options with VBA

  1. Shlomi

    Hi Kumar,
    Many Thanks for sharing your knowlage.
    Is it possiable to write the part you select those two items from References into the VBA code?
    So, if i’ll send the file to someone that is not famileir with VBA whatsoever, the code will run correctly ?

    Reply
  2. Amit Bodhwani

    Hi Sir,
    I follow your videos and after getting motivated I have done some automation work also but I am stuck at one point.
    I have a drop down list on a webpage when I click on the 10th option manually without automation then after clicking two new textboxes will pop up and that will only pop up when I click on that 10th option manually.
    But Using selectedIndex=10 it is only selecting that option and the other two textboxes are not coming,l tried onfocus and click event also but not got the success.
    When I did inspect element on the webpage I could see that in the form tag there is onsubmit call. For example onsubmit selectnext(this.somevalues) What I think if I am not wrong is if Iclick on any option in the drop down list that only onsubmit is getting called and other two text boxes are poping up.But I want to do it using vba code I am able to do everything else,I am only stuck with this last part.Please help regarding this issue.
    Thank You Sir

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *