0

I am trying to do something very similar to the below existing example: reference problem

With one small exception, I need to pull only the rating and # of reviews for this listing into 2 separate cells in Excel.

How would I do this in a way without pulling the entire site's data? It seems I need to call a specific html tag or use a command to do this, but I don't know what it is.

Please Help!

Community
  • 1
  • 1
user3558159
  • 3
  • 3

1 Answers1

0

This code will retrieve the two pieces of information you requested and place them on the activesheet

Sub test()
    my_url = "http://www.yelp.com/biz/if-boutique-new-york"
    Set html_doc = CreateObject("htmlfile")
    Set xml_obj = CreateObject("MSXML2.XMLHTTP")

    xml_obj.Open "GET", my_url, False
    xml_obj.send
    html_doc.body.innerhtml = xml_obj.responseText
    Set xml_obj = Nothing

    Set Results = html_doc.body.getElementsByTagName("i")
    For Each itm In Results
        If InStr(1, itm.outerhtml, "star-img", vbTextCompare) > 0 Then
            numb_stars = itm.getAttribute("title")
            Exit For
        Else
        End If
    Next

    Set Results = html_doc.body.getElementsByTagName("span")
    For Each itm In Results
        If InStr(1, itm.outerhtml, "reviewCount", vbTextCompare) > 0 Then
            numb_rev = itm.innertext
            Exit For
        Else
        End If
    Next

    ActiveCell = numb_stars
    ActiveCell.Offset(1, 0) = numb_rev
End Sub
ron
  • 1,456
  • 3
  • 18
  • 27
  • This is amazing! Actually this is the first question I've asked on this site, so I'm happy to see such good, quick responses! Thank you! One more thing, is it possible to dynamically change the url using cell reference on my_url variable? And run the script on lets say a list of 10 different urls (from a1:a10)? – user3558159 Apr 23 '14 at 20:23
  • Yes, dynamically updating the url is not a problem. The method you suggested should work fine. If my answer was helpful, please mark it as "accepted", thanks. – ron Apr 23 '14 at 21:18
  • Also is there a way to speed up the query time? – user3558159 Apr 23 '14 at 21:32
  • This is the fastest method I am aware of, certainly faster than opening an instance of IE – ron Apr 23 '14 at 21:43