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

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. You can contact me if you need any assistance. (You can use contact form in the side bar of this blog.)

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