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. — Blackhawk
– Blackhawk, Commented Oct 20, 2014 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
– Blackhawk, Commented Oct 20, 2014 at 15:12

Aiken · Accepted Answer · 2014-10-20 15:16:19Z

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, 2014 at 15:16

Aiken

2,6682 gold badges20 silver badges25 bronze badges

answered Oct 20, 2014 at 15:07

Leo Chapiro

14k8 gold badges67 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Petrik Over a year ago

Thanks, I know it's something that easy but couldn't find out. Thanks a lot

Collectives™ on Stack Overflow

Excel VBA loop with sending request to Api

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related