I'm trying to pull home value estimates from Zillow.com. My code gets hung up on element ID but everything appears to be correct to me. My spreadsheet that supplies the addresses to retreive from the website are setup as follows:
Street Full Address Zillow Estimate Sqft Bath
1 1380 E WASHINGTON AVE 92019
1 717 W BEL ESPRIT CIRCLE 92069
1 701 MADISON AVE 91910
1 642 N FIG ST 92025
Sub QuotesControl()
Dim objIE As InternetExplorer
Set objIE = CreateObject("InternetExplorer.Application")
Call Get_Quotes(objIE, "E", "LV")
Call Get_Quotes(objIE, "F", "BD")
Call Get_Quotes(objIE, "G", "BA")
Set objIE = Nothing
End Sub
Private Function Get_Quotes(ByRef ie As Object, _
ByVal Target As String, _
ByVal QuoteType As String)
Dim hDoc As Object 'MSHTML.HTMLDocument
Dim lRow As Long
Dim str As String
Dim strEst As String
With ie
.Visible = True
.navigate "http://www.zillow.com/"
For lRow = 2 To Range("A" & Rows.Count).End(xlUp).Row
Call CheckBusy(ie)
Set hDoc = .document
hDoc.getElementsByName("citystatezip").Item(0).Value = Sheets("MAIN").Range("D" & lRow)
hDoc.getElementsById("GOButton").Click
Call CheckBusy(ie)
Set hDoc = .document
Call CheckBusy(ie)
str = CStr(hDoc.getElementById("address-result").innerHTML)
Call CheckBusy(ie)
If InStr(1, str, QuoteType) = 0 Then
strEst = ""
Else
strEst = Mid(str, InStr(1, str, QuoteType) + 5, _
InStr(InStr(1, str, QuoteType) + 5, str, Chr(34)) - (InStr(1, str, QuoteType) + 5))
End If
Worksheets("MAIN").Range(Target & lRow) = strEst
Next lRow
End With
End Function
Private Sub CheckBusy(ByRef ie As Object)
With ie
Do While .Busy: DoEvents: Loop
Do While .readyState <> 4: DoEvents: Loop
End With
End Sub
Bookmarks