Excel VBA loop with sending request to Api

Question

I created a successfully working macro that is sending calls and receiving responses from a web-service. Now I would like to send different calls (from column A) and write the resposne to column B. First request is sent successfully. However, I always get a run time error on the second request.

Here is my macro:

Sub API_CALL()

    Dim sURL As String
    Dim sEnv As String
    Dim xmlDoc As New DOMDocument
    Dim sEnvlength As Integer
    Dim responseText As String

    Dim MyString As String
    Dim LastRow As Long

    LastRow = Cells(Rows.Count, "A").End(xlUp).Row

    For i = 2 To LastRow

        MyString = Cells(i, 1)

        Set ObjHttp = New MSXML2.XMLHTTP
        sURL = "https://example.com/WebService.asmx"

        sEnv = sEnv & "<?xml version=""1.0"" encoding=""utf-8""?>"
        sEnv = sEnv & "<soap:Envelope xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns:soap=""http://schemas.xmlsoap.org/soap/envelope/"">"
        sEnv = sEnv & "  <soap:Body>"
        sEnv = sEnv & "      <Command>" & MyString & "</Command>"
        sEnv = sEnv & "    </msg>"
        sEnv = sEnv & "  </soap:Body>"
        sEnv = sEnv & "</soap:Envelope>"

        sEnvlength = Len(sEnv)

        ObjHttp.Open "POST", sURL, False

        ObjHttp.setRequestHeader "Content-Type", "text/xml; charset=utf-8"
        ObjHttp.setRequestHeader "Content-Length", sEnvlength

        ObjHttp.send (sEnv)

        xmlDoc.LoadXML (ObjHttp.responseText)
        responseText = xmlDoc.SelectNodes("//CommandResult")(0).Text 'on next i (=2) I got Error '91' - object variable or With block variable not set

        Cells(i, 2) = responseText

        Set xmlDoc = Nothing
        Set ObjHttp = Nothing

    Next i

End Sub

Any help would be really appreciated!!!

It's possible that you are making the requests too quickly - that their site might be protecting against DoS or something. You could test that by throwing in a delay of a half a second each iteration of the loop and see if the requests go through. If so, you can adjust the time delay downward until you find the sweet spot. There are a couple options for pausing code in VBA, which you can find [here](http://stackoverflow.com/questions/1544526/how-to-pause-for-specific-amount-of-time). — Blackhawk, Oct 20 '14 at 15:09
You could also try inspecting the `ObjHttp.responseText` when the code throws the error. That is likely to show you what the problem is. For example, it might be an html http error page instead of the requested content. Inspect the other state information of `ObjHttp` for good measure and you are sure to find something revealing. — Blackhawk, Oct 20 '14 at 15:12

score 2 · Accepted Answer · edited Oct 20 '14 at 15:16

2

You're missing sEnv = "" in your loop.

That is why your sEnv will always be wrong from the second loop onwards. Try this:

 For i = 2 To LastRow

    MyString = Cells(i, 1)

    Set ObjHttp = New MSXML2.XMLHTTP
    sURL = "https://example.com/WebService.asmx"

    ' Clear string first ...
    sEnv = ""

    sEnv = sEnv & "<?xml version=""1.0"" encoding=""utf-8""?>"
    ' ... and so on ...

edited Oct 20 '14 at 15:16

Aiken

2,628
2
18
25

answered Oct 20 '14 at 15:07

Leo Chapiro

13,678
8
61
92

1

Thanks, I know it's something that easy but couldn't find out. Thanks a lot – Petrik Oct 20 '14 at 15:14

Excel VBA loop with sending request to Api

1 Answers1