December 4, 2015

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 “”
‘Wait until IE has loaded the web page

Do While ie.readyState <> READYSTATE_COMPLETE

Application.StatusBar = “Loading Web page …”



‘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

7 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

    1. Hi
      I am facing the same issue…if you have some solution..pls will help

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

    1. I have been trying to select an option of a field which does not allow me to do it in the way that is handled in the example.

      the html code is like this ..

      Select …
      PAZ170614 MC PAZ SA DE CV

      How to choose an option in a case like this?

  4. Hi Sir

    I used the above code to scrape the data from a web page into excel and it worked wonderfully. Thank you for a great article.
    What I need now, is a slight variant, but basically the same process. Instead of accessing the website itself, through the URL path, I have been sent/passed the extraction of the HTML code as an input variable. Please can you show me the difference in approach to reading the input variable as apposed to reading the actual website, or is this even possible?

Comments are closed.