Pulling Data From a bunch of urls

Question

New to vba. Just starting to learn. I want to pull some specific data from a website.Code I am trying to modify is from Ron Retrieving specific data from website through excel.

Now this code work on a single url. I have urls in Column A of excel sheet and I want to macro to go one by one to all urls and paste results in Column B C D respectively.

Tried as best as my limited knowledge.

Regards

Stack Overflow is a site for programming enthusiasts. The best way to get help here is to first try something. If you get stuck, do some research, make an attempt at fixing things yourself, and only then ask a specific question about your attempts, showing what you have tried. Questions asking for complete solutions without demonstrating research effort usually get downvoted and closed. — Jean-François Corbett, Mar 30 '15 at 09:07
Dear Jean, I tried my best ,as per my knowledge, and than came here. It's very easy to point finger than to provide a solution. regards — Raj, Mar 30 '15 at 16:32
Stack Overflow is a site for programming enthusiasts. Beginners are welcome, if they ask specific questions showing what they have tried. Your question does not meet these criteria. — Jean-François Corbett, Mar 30 '15 at 16:41

score -1 · Accepted Answer · answered Mar 30 '15 at 15:45

The main sub will get the ratings and the number of reviews for each URL in column A and will place them in Column B and C. I hope this help you a little.

Sub main()
Dim l As Long
l = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To l
test Range("A" & i)
Next
End Sub

Sub test(URL As Range)
    my_url = URL.Value
    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

    URL.Offset(0, 1) = numb_stars
     URL.Offset(0, 2) = numb_rev
End Sub

Preview of my output:

enter image description here

Thanks A lot Bro.Working Fine. Code also made Clear what was I doing Wrong.Thanks a lot — Raj, Mar 30 '15 at 16:30

Pulling Data From a bunch of urls

1 Answers1