Getting error while extracting the data from webpage

Question 1

I’ve formulated the following code to retrieve data from the webpage provided: https://www.redfin.com/NV/Fallon/630-Serpa-Pl-89406/unit-Fallon/home/178255574?utm_source=android_share&utm_medium=share&utm_nooverride=1&utm_content=link&2010988919=variant&utm_campaign=homecard_share.

However, I’m encountering an ‘object required’ error at the line:
sqFt = html.getElementsByClassName(“statsLabel”)(0).NextSibling.getElementsByClassName(“statsValue”)(0).innerText.

I’m hopeful that someone can offer assistance to rectify this issue, enabling the code to successfully extract the pertinent information from the webpage.

Your guidance would be highly appreciated. Thank you.

Sub ExtractingRedfin()
    Dim url As String
    Dim ie As Object
    Dim html As Object
    Dim Lastrow As Long
    Lastrow = Sheet1.Cells(Sheet1.Rows.Count, 1).End(xlUp).Row
    
    ' URL of the webpage to scrape
    url = Sheet1.Cells(Lastrow, 1).Value
    
    ' Create a new Internet Explorer instance
    Set ie = CreateObject("InternetExplorer.Application")
    ie.Visible = False ' Set to True if you want to see the browser
    
    ' Navigate to the URL
    ie.navigate url
    
    ' Wait for the browser to finish loading the page
    Do While ie.readyState <> 4 Or ie.Busy
        DoEvents
    Loop
    
    ' Get the HTML content of the page
    Set html = ie.document
    
    Dim beds As String
    Dim baths As String
    Dim sqFt As String
    Dim price As String
    Dim est As String
    Dim address As String
    Dim cityStateZip As String
    
    beds = html.getElementsByClassName("statsValue")(0).innerText
    baths = html.getElementsByClassName("statsValue")(1).innerText
    sqFt = html.getElementsByClassName("statsLabel")(0).NextSibling.getElementsByClassName("statsValue")(0).innerText
    price = html.getElementsByClassName("info-block price")(0).getElementsByClassName("statsValue")(0).innerText
    est = html.getElementsByClassName("info-block price")(0).getElementsByClassName("statsLabel")(0).innerText
    address = html.getElementsByClassName("street-address")(0).innerText
    cityStateZip = html.getElementsByClassName("citystatezip")(0).innerText
    

    ie.Quit
    
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet3")
    
    ws.Cells(2, 1).Value = beds
    ws.Cells(2, 2).Value = baths
    ws.Cells(2, 3).Value = sqFt
    ws.Cells(2, 4).Value = price
    ws.Cells(2, 5).Value = est
    ws.Cells(2, 6).Value = address
    ws.Cells(2, 7).Value = cityStateZip
End Sub

Question 2

It would appear the website has been restructured since your code last ran successfully. Try replacing the relevant lines with

beds = html.getElementsByClassName("statsValue")(1).innerText
baths = html.getElementsByClassName("statsValue")(2).innerText
sqFt = html.getElementsByClassName("stat-block sqft-section")(0).getElementsByClassName("statsValue")(0).innerText
price = html.getElementsByClassName("stat-block beds-section")(0).getElementsByClassName("statsValue")(0).innerText
est = html.getElementsByClassName("stat-block beds-section")(0).getElementsByClassName("est-monthly-payment")(0).innerText
address = html.getElementsByClassName("street-address")(0).innerText
cityStateZip = html.getElementsByClassName("dp-subtext bp-cityStateZip")(0).innerText

… while this works just now, naturally there is no guarantee how long this will work (if the website is restructured again) .

If you are interested, read more here W3Schools and here MS Docs.

Leave a Comment Cancel reply