How to scrape option values from a website using VBA

Question

I am trying to fetch the names and values of locations from a website page. For example: I want to take the value 10 and label " Johannesburg OR Tambo International Airport" and insert it into cell B3 and B4 respectively and then loop it for all optgroups. I get an error "Object doesn't support this property or method." Im sure my code has multiple issues. any assistance will be greatly appreciated. My code is as follows:

Sub test1()

''''''''''''''''''''''''''''This part states the variables and their dimenstions.
    Dim appIE As Object
    Dim ws As Worksheet
    Dim wb As Workbook
    Dim o

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

i = 2

    Set wb = Application.Workbooks("Test2")
    Set ws = wb.Worksheets("Europcar Branches")
    Set appIE = CreateObject("internetexplorer.application")

'Navigate to Europcar
'Open internet explorer
With appIE
.Navigate "https://www.europcar.co.za"
.Visible = True
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Application.Wait (Now + TimeValue("0:00:03"))
Do While appIE.busy
    DoEvents
    Application.Wait (Now + TimeValue("0:00:05"))
    Loop
Application.Wait (Now + TimeValue("0:00:02"))

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''


 Set entry = appIE.document.getElementById("PickupBranch_BranchID_id")
For Each o In entry.getElementsByName("optgroup")
Cells(i, 3).Value = o.Value
    For Each p In entry.getElementsByName("optgroup").Options
    Cells(i, 4).Value = p.innerText
   i = i + 1
Exit For
Next
Exit For

Next
'
'.Navigate "https://www.europcar.co.za"
'.Visible = True

Application.Wait (Now + TimeValue("0:00:01"))

Do While appIE.busy
    DoEvents
    Application.Wait (Now + TimeValue("0:00:03"))
    Loop

End With

appIE.quit
    Set appIE = Nothing

End Sub

A section of Html is as follows:

QHarr · Accepted Answer

The following shows you how to do for one drop down (it gathers all the optgroups within). It avoids using a browser and goes with the faster xmlhttp request. I use getElementById, to get the parent select element, and then getElementsByClassName to retrieve the child option tag elements. I loop from 1 to avoid the empty first element.

References (VBE > Tools > References):

Microsoft HTML Object Library

VBA:

Option Explicit
Public Sub GetOptions()
    Dim html As Object, ws As Worksheet, headers()
    Dim i As Long, r As Long, c As Long, numRows As Long

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set html = New HTMLDocument
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", "https://www.europcar.co.za/", False
        .send

        html.body.innerHTML = .responseText

        Dim pickupBranches As Object, pickupBranchResults()

        Set pickupBranches = html.getElementById("PickupBranch_BranchID_id").getElementsByTagName("option")
        headers = Array("Pickup Location", "option value")
        numRows = pickupBranches.Length - 1

        ReDim pickupBranchResults(1 To numRows, 1 To 2)

        For i = 1 To numRows
            pickupBranchResults(i, 1) = pickupBranches.item(i).innerText
            pickupBranchResults(i, 2) = pickupBranches.item(i).Value
        Next

        With ws
            .Cells(1, 1).Resize(1, UBound(headers) + 1) = headers
            .Cells(2, 1).Resize(UBound(pickupBranchResults, 1), UBound(pickupBranchResults, 2)) = pickupBranchResults
        End With
    End With
End Sub

How to scrape option values from a website using VBA

Answers (1)

Related Questions