Zillow API VBA

Hello Excel World,

I downloaded a VBA code off the internet to get me zillow.com information (real estate website that gives rent estimates and property estimates among other details). All that you do is plug in the address and information is spit out. It works very well with one exception when it gets stuck. A problem occurs when it tries to pulls a rent amount (called rent Zestimate) or a property value (called Zestimate) that is "UNAVAILABLE", then run-time error 91 pops up. My goal is to allow the code to keep running and/or simply skip over that address and it's details and continue down the list.

Below is the code, I tried to make it in a cool little box so it doesn't take up so much space but gave up.

Thank you,
CrazyDg99

Sub ZillowXML()
' Miscrosoft XML v6.0 must be enabled from the VBA editor
' To enable, go to Tools>References and check the box next to "Miscrosoft XML v6.0"

' Zillow API overview and signup
' http://www.zillow.com/howto/api/APIOverview.htm

' Zillow Web Service ID
ZWSID = "X1-ZWz199mpm4y77v_3e7gq"

' Number of header columns
Headers = 2

' Columns containing addresses
Address = "A"
City = "B"
State = "C"
Zip = "D"

' Columns to return data
ErrorMessage = "E"
HomeDetails = "F"
Graphsanddata = "G"
Mapthishome = "H"
Comparables = "I"
latitude = "J"
longitude = "K"
ZAmount = "L"
LastUpdate = "M"
zLow = "N"
zHigh = "O"
Rent = "P"
RentLastUpdate = "Q"
RentLow = "R"
RentHigh = "S"
Region = "T"
Overview = "U"
FSBO = "V"
forsale = "W"


' DO NOT EDIT BELOW THIS LINE UNLESS YOU KNOW WHAT YOU'RE GETTING INTO
'_______________________________________________________________________________

' Changes to make
' Automatically read address from a MsgBox dialog and transpose that list to a new sheet
' Automatically create new columns to put data in
' Convert new data range to a table and name it "Zillow"

Dim xmldoc As MSXML2.DOMDocument60
Dim xmlNodeList As MSXML2.IXMLDOMNodeList
Dim myNode As MSXML2.IXMLDOMNode
Dim WS As Worksheet: Set WS = ActiveSheet

' Seth column to display API URL for troubleshooting
'xmlURL = "E"

' Tell user the code is running
Application.StatusBar = "Starting search"

' Count Rows
LastRow = Cells(Rows.Count, 1).End(xlUp).Row

'___________________________________________________

' Begin looping through rows to find and insert data
    ' i = 3 leaves the first two row as column headers
    For i = Headers + 1 To LastRow
    
        'Clear previous data from cells
        WS.Range(ErrorMessage & i) = ""
        WS.Range(HomeDetails & i) = ""
        WS.Range(Graphsanddata & i) = ""
        WS.Range(Mapthishome & i) = ""
        WS.Range(Comparables & i) = ""
        WS.Range(latitude & i) = ""
        WS.Range(longitude & i) = ""
        WS.Range(ZAmount & i) = ""
        WS.Range(LastUpdate & i) = ""
        WS.Range(zLow & i) = ""
        WS.Range(zHigh & i) = ""
        WS.Range(Rent & i) = ""
        WS.Range(RentLastUpdate & i) = ""
        WS.Range(RentLow & i) = ""
        WS.Range(RentHigh & i) = ""
        WS.Range(Region & i) = ""
        WS.Range(Overview & i) = ""
        WS.Range(FSBO & i) = ""
        WS.Range(forsale & i) = ""

        
        ' Create Zillow API URL
        rowAddress = WS.Range(Replace(Address, " ", "+") & i)
        rowCity = WS.Range(City & i)
        rowState = WS.Range(State & i)
        rowZip = WS.Range(Zip & i)
        ' Comment out to use testing URL
        URL = "http://www.zillow.com/webservice/GetSearchResults.htm?zws-id=" & ZWSID & "&address=" & rowAddress & "&citystatezip=" & rowCity & "%2C+" & rowState & "%2C+" & rowZip & "&rentzestimate=true"
        ' Local URL for testing
        'URL = ("C:\Users\John\Dropbox\Excel\ZillowXML.xml")
        
        ' Uncomment to Display API URL for troubleshooting
        'WS.Range(xmlURL & i) = ""
        'WS.Range(xmlURL & i).Formula = "=HYPERLINK(""" & URL & """,""API URL"")"
                   
         ' Tell user what address is being searched for
         Application.StatusBar = "Retrieving: " & i & " of " & LastRow - Headers & ": " & rowAddress & ", " & rowCity & ", " & rowState
        
        'Open XML page
        Set xmldoc = New MSXML2.DOMDocument60
        xmldoc.async = False
        
        ' Check XML document is loaded
        If xmldoc.Load(URL) Then
        
            
            Set xmlMessage = xmldoc.SelectSingleNode("//message/text")
            Set xmlMessageCode = xmldoc.SelectSingleNode("//message/code")
            
            ' Check for an error message
            If xmlMessageCode.Text <> 0 Then
            
                ' Return error message
                WS.Range(ErrorMessage & i) = xmlMessage.Text
                
            Else
                ' Get XML data from Zillow
                Set xmlHomeDetails = xmldoc.SelectSingleNode("//response/results/result/links/homedetails")
                Set xmlGraphsAndData = xmldoc.SelectSingleNode("//response/results/result/links/graphsanddata")
                Set xmlComparables = xmldoc.SelectSingleNode("//response/results/result/links/comparables")
                Set xmlMapthishome = xmldoc.SelectSingleNode("//response/results/result/links/mapthishome")
                ' Push data to preadsheet
                If xmlHomeDetails Is Nothing Then
                    WS.Range(HomeDetails & i) = "No home details available"
                Else
                    WS.Range(HomeDetails & i).Formula = "=HYPERLINK(""" & xmlHomeDetails.Text & """,""Zillow Details"")"
                End If
                If xmlGraphsAndData Is Nothing Then
                    WS.Range(Graphsanddata & i) = "No graphs available"
                Else
                    WS.Range(Graphsanddata & i).Formula = "=HYPERLINK(""" & xmlGraphsAndData.Text & """,""Graphs & Data"")"
                End If
                If xmlComparables Is Nothing Then
                    WS.Range(Comparables & i) = "No comparables available"
                Else
                    WS.Range(Comparables & i).Formula = "=HYPERLINK(""" & xmlComparables.Text & """,""Zillow Comparables"")"
                End If
                If xmlMapthishome Is Nothing Then
                    WS.Range(Mapthishome & i) = "No map available"
                Else
                    WS.Range(Mapthishome & i).Formula = "=HYPERLINK(""" & xmlMapthishome.Text & """,""Zillow Map"")"
                End If
                   
                ' Retrieve Lat & Long
                Set xmlLatitude = xmldoc.SelectSingleNode("//response/results/result/address/latitude")
                Set xmlLongitude = xmldoc.SelectSingleNode("//response/results/result/address/longitude")
                ' Push data to preadsheet
                WS.Range(latitude & i) = xmlLatitude.Text
                WS.Range(longitude & i) = xmlLongitude.Text
                
                
                ' Retrieve Zestimate
                Set xmlZAmount = xmldoc.SelectSingleNode("//response/results/result/zestimate/amount")
                Set xmlZLastUpdate = xmldoc.SelectSingleNode("//response/results/result/zestimate/last-updated")
                Set xmlZValLow = xmldoc.SelectSingleNode("//response/results/result/zestimate/valuationRange/low")
                Set xmlZValHigh = xmldoc.SelectSingleNode("//response/results/result/zestimate/valuationRange/high")
                ' Push data to preadsheet
                WS.Range(ZAmount & i) = xmlZAmount.Text
                WS.Range(ZAmount & i).NumberFormat = "$#,##0_);($#,##0)"
                WS.Range(LastUpdate & i) = xmlZLastUpdate.Text
                WS.Range(zLow & i) = xmlZValLow.Text
                WS.Range(zLow & i).NumberFormat = "$#,##0_);($#,##0)"
                WS.Range(zHigh & i) = xmlZValHigh.Text
                WS.Range(zHigh & i).NumberFormat = "$#,##0_);($#,##0)"
                
                ' Retrieve RentZestimate
                Set xmlRZAmount = xmldoc.SelectSingleNode("//response/results/result/rentzestimate/amount")
                Set xmlRZLastUpdate = xmldoc.SelectSingleNode("//response/results/result/rentzestimate/last-updated")
                Set xmlRZValLow = xmldoc.SelectSingleNode("//response/results/result/rentzestimate/valuationRange/low")
                Set xmlRZValHigh = xmldoc.SelectSingleNode("//response/results/result/rentzestimate/valuationRange/high")
                ' Push data to preadsheet
                WS.Range(Rent & i) = xmlRZAmount.Text
                WS.Range(Rent & i).NumberFormat = "$#,##0_);($#,##0)"
                WS.Range(RentLastUpdate & i) = xmlRZLastUpdate.Text
                WS.Range(RentLow & i) = xmlRZValLow.Text
                WS.Range(RentLow & i).NumberFormat = "$#,##0_);($#,##0)"
                WS.Range(RentHigh & i) = xmlRZValHigh.Text
                WS.Range(RentHigh & i).NumberFormat = "$#,##0_);($#,##0)"

                ' Retrieve LocalRealEstate
                Set xmlRegion = xmldoc.SelectSingleNode("//response/results/result/localRealEstate/region")
                Set xmlOverview = xmldoc.SelectSingleNode("//response/results/result/localRealEstate/region/links/overview")
                Set xmlFSBO = xmldoc.SelectSingleNode("//response/results/result/localRealEstate/region/links/forSaleByOwner")
                Set xmlForSale = xmldoc.SelectSingleNode("//response/results/result/localRealEstate/region/links/forSale")
                ' Push data to preadsheet
                If xmlRegion Is Nothing Then
                    WS.Range(Region & i) = "No region information available"
                Else
                    WS.Range(Region & i).Formula = "=HYPERLINK(""" & xmlRegion.Text & """,""Regional Details"")"
                End If
                If xmlOverview Is Nothing Then
                    WS.Range(Overview & i) = "No region overview available"
                Else
                    WS.Range(Overview & i).Formula = "=HYPERLINK(""" & xmlOverview.Text & """,""Region Overview"")"
                End If
                If xmlFSBO Is Nothing Then
                    WS.Range(FSBO & i) = "No FSBO available"
                Else
                    WS.Range(FSBO & i).Formula = "=HYPERLINK(""" & xmlFSBO.Text & """,""For Sale By Owner"")"
                End If
                If xmlForSale Is Nothing Then
                    WS.Range(forsale & i) = "No local For Sale Information"
                Else
                    WS.Range(forsale & i).Formula = "=HYPERLINK(""" & xmlForSale.Text & """,""Active For Sale"")"
                End If
                
                
            End If
            
       ' Document failed to load statement
       Else
       WS.Range(ErrorMessage & i) = "The document failed to load. Check your internet connection."
       
       End If
    
    ' Loop to top for next row
    Next i
    
' Tell user the search is complete
Application.StatusBar = "Search complete!"

End Sub

Code Tags Added
Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html



(I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)

You need to set a nothing range for all nodes which are empty.
If you look at one of your line of code.

Set xmlHomeDetails = xmldoc.SelectSingleNode("//response/results/result/links/homedetails")

 If xmlHomeDetails Is Nothing Then
                    WS.Range(HomeDetails & i) = "No home details available"
                Else
                    WS.Range(HomeDetails & i).Formula = "=HYPERLINK(""" & xmlHomeDetails.Text & """,""Zillow Details"")"
                End If

if the info is empty, it does not error.
You also need to add to other parts of the code in which there is no data.
For instance:

 Set xmlZAmount = xmldoc.SelectSingleNode("//response/results/result/zestimate/amount")

should be

  If xmlZAmount Is Nothing Then
                    WS.Range(ZAmount & i) = "No Amount available"
                    
                  Else
                    WS.Range(ZAmount & i) = xmlZAmount.Text
                  End If

Originally Posted by pcollins23

CrazyDg - do you know how to break out the property details into additional columns instead of including the link? I'm trying to identify the year property's were built without clicking the link. Any help would be appreciated

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original. Please start a new thread - See Forum rule #4

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

This thread is over 2 years old, kr007, so if the update isnt here now, it probably wont be updated.