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, ""

Upper Case, Lower Case and Proper Case

In this post I will show you how to use VBA functions "UCase", "LCase" and worksheet function Proper to format the text in excel sheet. I will explain this using below example. Assume we have list of names like this.

So we have 10 names in sheet 1. As you can see that names are not in a proper format. So now let's look at how to convert this 10 names to upper case first. You can use below code to do that.

Sub ConvertToUpperCase() Dim WS_Input As Worksheet Dim WS_Result As Worksheet Dim i As Integer Set WS_Input = Worksheets("Sheet1") Set WS_Result = Worksheets.Add For i = 1 To 10     WS_Result.Range("A" & i).Value = UCase(WS_Input.Range("A" & i).Value) Next i End Sub

So this will convert the names to upper case and list them in a new sheet. So this is the result you will get after running the code.

You can see that all the letters have converted to upper case. So now let's look at how to convert that names to lower case.  Below code will do that.

Sub ConvertToLowerCase() Dim WS_Input As Worksheet Dim WS_Result As Worksheet Dim i As Integer Set WS_Input = Worksheets("Sheet1") Set WS_Result = Worksheets.Add For i = 1 To 10     WS_Result.Range("A" & i).Value = LCase(WS_Input.Range("A" & i).Value) Next i End Sub

You can change the limits of for next loop to do this for any amount of rows. Also you can modify the code to replace existing text in sheet 1 with result text. After running this macro result will look like this.

So now let's look at how to convert these names to proper case or title case. This is little different than other two. Because in earlier two cases, we used VBA functions. But this time we need to use a worksheet function. Because of that Proper(WS_Input.Range("A" & i).Value) will not work this time. Instead we have to use Application.WorksheetFunction.Proper(WS_Input.Range("A" & i).Value). Here is the full code.

Sub ConvertToProperCase() Dim WS_Input As Worksheet Dim WS_Result As Worksheet Dim i As Integer Set WS_Input = Worksheets("Sheet1") Set WS_Result = Worksheets.Add For i = 1 To 10     WS_Result.Range("A" & i).Value = Application.WorksheetFunction.Proper(WS_Input.Range("A" & i).Value) Next i End Sub

You will get below result after running this macro.

So modify above codes to suit with your requirements and improve your workbooks.

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.