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

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

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

  1. 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 ?

  2. 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

  3. Hi,
    How do you go about the following. My brain has been trying to work it out for a week now and still nothing works.
    3 Dropdown boxes in website. I am able to fill in all data boxes from vba.
    But
    When it comes to the dropdowns, I can place text into the dropdown but needs and action for the next dropdown to be activated for selection. (eg. Dropdown 1, select Region [i can fill the correct region but will not allow anything into dropdown 2] – you have to arrow down to open the dropdown then actually select the item, then the dropdown 2 will activate.
    So Dropdown 1 Regions then passes filter down to Dropdown 2 Provinces which then passes filter down to dropdown 3 City.
    Any guidance would be appreciated as I have tried most things people have written.

Leave a Reply

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