Extract Data from a URL

Hi Shucks,

I have been searching for a solution for the last one and half week, but without any positive result. Finally I bumped into your post which is actually the solution, here is the link

http://www.excelforum.com/excel-programming-vba-macros/782361-extract-data-from-a-url-stored-in-a-cell.html

posted and solved by you.
When I saw this I was in joy and all my stress perseverance just vanished. You have no clue what this means to me.

I have thousands and thousands of URLs to extract a specific data thats all, but it surely is killing my time and its becoming forever the same work.

Shucks, being honest am not a techie or programmer and am a noob in vba. I kindly request you to help me in this, and I really value your time. I will attach the sample work files I have done manually in which I manually go click on the url in the cell then go to the web page and copy the data and paste it in the adjacent cell. Please take a look sample.

I kindly want you to create a macro or a vba which will extract the relevant data to the relevant column.

Hello Shucks,
I believe you are still with this forum. I already sent you a mail and the attachment is very much here in this thread. Kindly help me.

Hi -

try;

Sub test()
Dim rng, lr As Long, a, res As String, x As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
rng = Range("a2:b" & lr)
For x = LBound(rng) To UBound(rng)
With CreateObject("MSXML2.XMLHTTP")
    .Open "GET", rng(x, 1), False
    .setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
    .send rng(x, 1)
    a = Split(.responsetext, "userbody")(1)
    res = WorksheetFunction.Clean(Left(a, InStr(a, "<!") - 1))
    If InStr(res, "<h5>") Then
        res = Left(res, InStr(res, "<h5>") - 1)
    End If
    rng(x, 2) = Replace(Replace(Replace(Replace(res, Chr(34) & ">", vbNullString), "<br>", Chr(10)), "</h2>", vbNullString), "<h2>", vbNullString)
End With
Next
Range("a2:b" & lr) = rng
End Sub

Regards,
Event

Hello Event21,

Guess what!! it works Phew!!! finally somebody helped me.
You made my day Event21, Now am like a HERO in the workplace.
You saved me from working like a DONKEY....
You are great
thanks a million

Hello Event21,

I really appreciate your quick response, I have still not tried it but I will try it right away.

Thanking you a million times, am now feeling a bit relaxed for getting responses to my query

Thanks, will reply once I try the code

Thanks to Kyle also for the rep.

Regards,
Event