Run-time error '5': Invalid procedure call or argument

Hello everybody,
this code was working fine on my other system, but when I try to use this on a different system, am getting "Run-time error '5': Invalid procedure call or argument",

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

I have colored red where i get this error, any help is greatly appreciated.

Thanks in Advance

What is the value of rng(x,1) when you get the error?
The .open method will return the '5' error if, for example, there is nothing in the cell and rng(x,1) evaluates to empty.

Maybe try changing
.Open "GET", rng(x, 1), False
to
.Open "GET", "http://www.google.com", False
and see if that works?

Hi Yulugar,

Thanks for the response, may be I will try it,

Hi Yudlugar,

am not so good at codes, this code was created for me by Event21 a forum contributor, its actual function was to extract description from craigslist based on the links, I will attach sample of it for your understanding, probably you will get a clear picture about it, please it would be of great help. I just had to enter the links in column 1 and I was able to extract Description on Column 2 from craigslist. If you could create an Excel with the code, it would be of great help. Am absolutely non technical person.

Thanking you in Advance

Hi Yudlugar,

I believe there was a macro enabled button on the sheet, So basically I had to copy the links from the Excel sheet and paste it on this Excel on specific column and then extract the description using the button, its been quite a while I used, there are thousands of these links for which I need to extract the description. Am just stuck. Please help

When I do it manually, am able to do it and all these links are from the past week, all links are live and fine. Am just not able to figure out how I did it last time. I am very sure that it was a button which i used with this code. all I had to do was to copy the links approximately 800 to 1000 links at a time, and then enter the button. It would fetch the description from each link in the next column. Am such numb skull, not able to figure out.

I understand the purpose of the sheet. Again:

What happens if you load up internet explorer (or another web browser) and try and access one of the sites in your sample? Try:
http://miami.craigslist.org/brw/pts/3949953392.html

What happens if you run this code:

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)
x = LBound(rng)
With CreateObject("MSXML2.XMLHTTP")
    .Open "GET", rng(x, 1), False
    .setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
    .send rng(x, 1)
    MsgBox .responsetext
End With
Range("a2:b" & lr) = rng
End Sub

Do you get a messagebox? What does it say?

Ok, try doing this:
-open the sample workbook you uploaded.
-Press Alt+F11
-Copy and paste this code and then run it (press F5)

Sub test1()
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)
x = "http://miami.craigslist.org/brw/pts/3949953392.html"
With CreateObject("MSXML2.XMLHTTP")
    .Open "GET", rng(x, 1), False
    .setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
    .send rng(x, 1)
    Range("A1") = .responsetext
    End With
Range("a2:b" & lr) = rng
End Sub

What do you get in cell A1 on the worksheet?

This is all I get just as in the image
Untitled.jpg

That is what you get if you press F5 in the excel sheet so I don't the code has actually ran, try again, I'll try and be more specific with the instructions:
-Open the sample workbook you gave me:
-Press Alt+F11 to open the visual basic editor
-Navigate to where your code is stored
-Replace it with this code:

Sub test1()
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)
x = "http://miami.craigslist.org/brw/pts/3949953392.html"
With CreateObject("MSXML2.XMLHTTP")
    .Open "GET", rng(x, 1), False
    .setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
    .send rng(x, 1)
    Range("A1") = .responsetext
    End With
Range("a2:b" & lr) = rng
End Sub

-Run the new code, click within the text between "sub" and "end sub" and press F5 or click the run button

Now do you get something in cell A1?

Its the same thing as in the image I sent you, not able to extract anything.

What happens if you run the macro from the menu rather than using the F5 shortcut?

runtime error 13, type mismatch

If you see in my previous post when I got this code, i had even attached the sample file and the contributor wrote this code for me, the link is http://www.excelforum.com/excel-prog...47#post3048047

Hello Yudlugar,

If I gave you a sample will you be able to write new code, it would be of great help. I use excel 2007

what about if you try and run this code:

Sub test1()
Dim x
x = "http://miami.craigslist.org/brw/pts/3949953392.html"
With CreateObject("MSXML2.XMLHTTP")
    .Open "GET", x, False
    .setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
    .send x
    MsgBox .responsetext
End With
End Sub

I got a new pop up, image attached below
NewImage.jpg

I don't think there is a problem with the code, looks like it works ok to me.

I'm trying to determine what response you get to the msxml2.xmlhttp request to see if I can determine what the problem is.

End of the day so I've tried to come up with another method that might work:

Option Explicit

Sub test()
Dim rng, lr As Long, a, res As String, x As Long
On Error GoTo not_found
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 = .responsetext
    
    a = Range("A23")
    
    a = Strings.Left(a, InStr(1, a, "<section class=""cltags"""))
    a = Strings.Right(a, Len(a) - InStr(1, a, "<section id=""postingbody"">") - 25)
    a = Replace(a, "<br>", "")
    a = Replace(a, "</section>", "")
    a = Replace(a, "<", "")
    rng(x, 2) = a
End With
next_for_loop:
Next
Range("a2:b" & lr) = rng
On Error GoTo 0
Exit Sub
not_found:
rng(x, 2) = "DESCRIPTION NOT FOUND"
Resume next_for_loop
End Sub

All the samples are which I showed you, comes up with a result "DESCRIPTION NOT FOUND", I think it works but its not extracting the data, the whole list is "DESCRIPTION NOT FOUND"

Ok good, sorry this is a bit of a pain, trying to debug remotely isn't fun.

What does this code give you:

Sub test1()
Dim x
x = "http://miami.craigslist.org/brw/pts/3949953392.html"
With CreateObject("MSXML2.XMLHTTP")
    .Open "GET", x, False
    .setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
    .send x
    MsgBox Split(.responsetext, "userbody")(1)
End With
End Sub

Yudlugar, I think something is happening, it is extracting now, but kind of weird am attaching the sample file along, I just want the description from each link, I think it is doing but something is wrong please take a look at the attachment. Its kind of extracting html, scripts etc

Thanks

Sorry, I forgot to take out a bit I included while testing, try this:

Sub test()
Dim rng, lr As Long, a, res As String, x As Long
On Error GoTo not_found
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 = .responsetext
    a = Strings.Left(a, InStr(1, a, "<section class=""cltags"""))
    a = Strings.Right(a, Len(a) - InStr(1, a, "<section id=""postingbody"">") - 25)
    a = Replace(a, "<br>", "")
    a = Replace(a, "</section>", "")
    a = Replace(a, "<", "")
    rng(x, 2) = a
End With
next_for_loop:
Next
Range("a2:b" & lr) = rng
On Error GoTo 0
Exit Sub
not_found:
rng(x, 2) = "DESCRIPTION NOT FOUND"
Resume next_for_loop
End Sub

Wow Yudlugar,

It worked, and it works better than before, I don't know what you did, but its awesome. Thanks a Million for this Yudlugar.

Regards
Bruce