Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

Web Scraping - Collect Options Inside Drop-down Lists

When we develop web scraping scripts we may need to collect items (options)  inside drop-down lists. So let's look at how to develop a code to collect options inside a select tag.

If you are new to Web Scraping, please read this post first.

Web Scraping - Basics

This is how drop-downs look like






Below is the source code of above drop-down list

<select d-start="1"  name="qty" class="name1"><option value="1" selected="selected">Quantity: 1</option><option value="2">Quantity: 2</option><option value="3">Quantity: 3</option></select>

So if you need to collect options inside this drop down list, you can use below code.

Dim QuantityString As String

Set objIE = CreateObject("InternetExplorer.Application")

objIE.Top = 0
objIE.Left = 0
objIE.Width = 800
objIE.Height = 600

objIE.Visible = True

objIE.Navigate ("Your url here")
 
Do
DoEvents
Loop Until objIE.readystate = 4

QuantityString = objIE.document.getElementsByClassName("name1")(0).innerText

So what it does is, it will store the options inside the drop-down to variable call "QuantityString"

If you want to see the result in the message box use below line.

MsgBox QuantityString

So it will look like this.











However sometimes these drop-down lists may in disabled status. So now let's look at how to detect if a particular drop down list is in a disable status. Here below is a sample HTML code of a disabled drop-down list.

<select disabled="disabled" dstart="1" class="name2"><option value="1" selected="selected">Quantity: 1</option><option value="2">Quantity: 2</option><option value="3">Quantity: 3</option></select>

So here what we can do is, first assign whole code inside Select tag to a new variable. Then check that string for disabled="disabled". You can use inbuilt VBA function like InStr to check that.

This is how you can do it.



Dim DisableCheckingString As String

Set objIE = CreateObject("InternetExplorer.Application")

objIE.Top = 0
objIE.Left = 0
objIE.Width = 800
objIE.Height = 600

objIE.Visible = True

objIE.Navigate ("Your url here")
 
Do
DoEvents
Loop Until objIE.readystate = 4

DisableCheckingString = objIE.document.getElementsByClassName("name2")(0).outerhtml

If InStr(1, DisableCheckingString, "disabled=""disabled", vbTextCompare) > 0 Then
    msgbox "This combo box is disabled"
End If

You can alter this code to suit with your requirements. Because you may not need to show message box when the drop down list is disabled in real life examples. Instead you may need to skip those drop downs or may be you will need to mention about them in the result pages you create.

Contact Form

Name

Email *

Message *

Popular Posts