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.

VBA Web Scraping - Identifying Disabled Dropdowns

In this post I will explain how to identify a disabled drop-down.

Think we need to gather data from drop downs of list of web pages. Assume list of urls are in column A of an Excel worksheet and we need to put the value of the drop-down to a column B.

First we need to find a disabled drop down and compare it's HTML code with HTML code of normal drop-down to find something unique part for disabled ones.

For an example here is a HTML code of a normal drop down.

<select start="1" min="1" step="1" ptext="Quantity: " name="tmp" class="drp qty">

And below is a HTML code of the disabled drop down.

<select start="1" min="1" step="1" ptext="Quantity: " disabled="disabled" name="tmp" class="drp qty">

So now we can see that disabled="disabled" is unique for HTML codes of disabled drop downs.
Because of that we can use Instr function to distinguish disabled drop downs.

DisableCheckingString = objIE.document.getElementsByClassName("drp qty")(0).outerhtml

If InStr(1, DisableCheckingString, "disabled=""disabled", vbTextCompare) > 0 Then
    WS.Range("B" & i).Value = "Drop-down disabled"
End If
   
So here is a full code of an example.

Dim WS As Worksheet

Dim QuantityString As String
Dim DisableCheckingString As String
Dim url As String

Set objIE = CreateObject("InternetExplorer.Application")

Set WS = ActiveSheet

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

objIE.Visible = True

'find last row
Lastrow = WS.Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row

For i = 1 To Lastrow

    url = WS.Range("A" & i).Value
   
    If WS.Range("A" & i).Value <> "" Then

        objIE.Navigate (url)
       
        Do
        DoEvents
        Loop Until objIE.readystate = 4
       
        Application.Wait (Now + TimeValue("0:00:03"))
       
        QuantityString = objIE.document.getElementsByClassName("drp qty")(0).innerText
       
        DisableCheckingString = objIE.document.getElementsByClassName("drp qty")(0).outerhtml
       
        WS.Range("B" & i).Value = QuantityString
               
        'Distinguish disabled ones
        If InStr(1, DisableCheckingString, "disabled=""disabled", vbTextCompare) > 0 Then
            WS.Range("B" & i).Value = "Out of Stock"
        End If
   
   
    End If

Next i

objIE.Quit

MsgBox "Completed!", vbInformation, ""

Contact Form

Name

Email *

Message *

Popular Posts