CODEWITHSUNDEEP
vbaapiexcelsoap
Petrik
Petrik

Reputation: 827

Excel VBA loop with sending request to Api

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!!!

Upvotes: 0

Views: 4718

Answers (1)

Leo Chapiro
Leo Chapiro

Reputation: 13994

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 ...

Upvotes: 2

Related Questions