none
Accessing Web Services via SOAP in VBA for Excel RRS feed

  • Question

  • Hello, 

    I am trying to use VBA to connect to web services. I have to cleanse the code, but the snippet below should give a general idea of what I am trying to do: 

    Dim authtoken As String, sEnvBegin As String, sEnvEnd As String, sURL As String
    
    Sub setEnvBeginEnd()
        sURL = "http://api-test.XXXX.net/default.asmx"
        
        sEnvBegin = "<?xml version=""1.0"" encoding=""utf-8""?>" & vbCr
        sEnvBegin = sEnvBegin & "<soap:Envelope xmlns:soap=""http://schemas.xmlsoap.org/soap/envelope/"" xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"">" & vbCr
        sEnvBegin = sEnvBegin & "<soap:Body>" & vbCr
        
        sEnvEnd = "</soap:Body>" & vbCr
        sEnvEnd = sEnvEnd & "</soap:Envelope>"
        
    End Sub
    
    Sub authenticate()
    
        Dim sEnv As String, sEnvMid As String, appGuid As String, compGuid As String, uName As String, pass As String
        Dim xHTTP As New MSXML2.XMLHTTP, xmlDoc As New DOMDocument
        
        Call setEnvBeginEnd
        
        appGuid = Sheets("Input").Range("B1")
        compGuid = Sheets("Input").Range("B2")
        uName = Sheets("Input").Range("B3")
        pass = Sheets("Input").Range("B4")
        
        sEnvMid = "<Authenticate xmlns=""http://www.XXXX.net/Services"">" & vbCr
        sEnvMid = sEnvMid & "<appId>" & appGuid & "</appId>" & vbCr
        sEnvMid = sEnvMid & "<companyId>" & compGuid & "</companyId>" & vbCr
        sEnvMid = sEnvMid & "<userName>" & uName & "</userName>" & vbCr
        sEnvMid = sEnvMid & "<password>" & pass & "</password>" & vbCr
        sEnvMid = sEnvMid & "</Authenticate>" & vbCr
        
        sEnv = sEnvBegin & sEnvMid & sEnvEnd
        
        With xHTTP
            .Open "Post", sURL, False
            .setRequestHeader "Host", "http://api-test.XXXXX.net"
            .setRequestHeader "Content-Type", "text/xml; charset=utf-8"
            .setRequestHeader "SOAPAction", "http://www.XXXX.net/Services/Authenticate"
            .send (sEnv)
            'xmlDoc.LoadXML .getAllResponseHeaders & vbCr & .Status & .statusText
            'xmlDoc.Save ThisWorkbook.Path & "\WebQueryResult.xml"
            Debug.Print sEnv
            Debug.Print .getAllResponseHeaders
            Debug.Print .responseText
            Debug.Print .Status
            Debug.Print .statusText
        End With
        
        
    End Sub
    


    Now, when I run this code I get a HTTP error 400 (Bad Request). When I monkey around with the SOAPAction header, I sometimes get a 500 error and an XML response saying the 'SOAPAction' request is not recognized. 

    Using Fiddler, I get the following header data:

    POST http://api-test.XXXX.net/default.asmx HTTP/1.1
    Accept: */*
    Accept-Language: en-us
    soapaction: http://www.XXXX.net/Services/Authenticate
    Content-Type: text/xml; charset=utf-8
    Accept-Encoding: gzip, deflate
    User-Agent: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.1; WOW64; Trident/6.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; .NET4.0C; .NET4.0E; InfoPath.3)
    Host: api-test.XXXX.net
    Content-Length: 519
    Connection: Keep-Alive
    Pragma: no-cache
    
    <?xml version="1.0" encoding="utf-8"?>
    <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <soap:Body>
    <Authenticate xmlns="http://www.XXXX.net/Services">
    <appId>12345678-0910-1112-1314-151617181920</appId>
    <companyID>21222324-2526-2728-2930-313233343536</companyId>
    <userName>Badasstronaut@troubleshot.com</userName>
    <password>God</password>
    </Authenticate>
    </soap:Body>
    </soap:Envelope>

    So why on earth is the 'SOAPAction' header coming in lower-case? Also, the XML matches a request that works, but this one will only return errors. 

    Any help on this would be greatly appreciated! 

    Tuesday, February 11, 2014 12:00 AM

All replies

  • MSXML2.XMLHTTP40 doesn't re-case the SOAPAction header.

    Using fiddler I also found that a few smarter clients had re-parsed my namespace.

    I got it working first in PowerShell then went back to excel and compared the request line by line. This showed me an obvious fix for my namespace.

    Jon

    Friday, May 15, 2015 9:28 PM