Excel 2016 Soap Web Service API Request

[ghanchi2000]

My Code

Private Sub Button1_Click() 'Set and instantiate our working objects
    Dim Req As Object
    Dim sEnv As String
    Dim Resp As New MSXML2.DOMDocument60
    Set Req = CreateObject("MSXML2.XMLHTTP")
    Set Resp = CreateObject("MSXML2.DOMDocument.6.0")
    Req.Open "Post", "http://webapp.tcscourier.com/codapi/Service1.asmx", False
    
 ' we create our SOAP envelope for submission to the Web Service
     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 & "  <soapenv:Body>"
     sEnv = sEnv & "   <GetCNDetailsByReferenceNumber xmlns=""http://202.61.51.93:6265/"">"
     sEnv = sEnv & "    <userName>user</User>"
     sEnv = sEnv & "    <password>pass</Pwd>"
     'Looks for SKU in active worksheet cell B3
     sEnv = sEnv & "    <customerReferenceNo>" & Range("B3").Value & "</customerReferenceNo>"
     sEnv = sEnv & "   </GetCNDetailsByReferenceNumber>"
     sEnv = sEnv & "  </soapenv:Body>"
     sEnv = sEnv & "</soapenv:Envelope>"
' Send SOAP Request
    Req.send (sEnv)


' Display results in MessageBox
    'MsgBox Req.responseText
    Resp.LoadXML Req.responseText
    Range("B6").Value = Resp.SelectSingleNode("//GetCNDetailsByReferenceNumberResponse/GetCNDetailsByReferenceNumberResult").Text
    
  'clean up code
    Set Req = Nothing
    Set Resp = Nothing
    
    
End Sub

Request Code

POST /codapi/Service1.asmx HTTP/1.1Host: webapp.tcscourier.com
Content-Type: text/xml; charset=utf-8
Content-Length: length
SOAPAction: "http://202.61.51.93:6265/GetCNDetailsByReferenceNumber"


<?xml version="1.0" encoding="utf-8"?>
<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/">
  <soap:Body>
    <GetCNDetailsByReferenceNumber xmlns="http://202.61.51.93:6265/">
      <userName>string</userName>
      <password>string</password>
      <customerReferenceNo>string</customerReferenceNo>
    </GetCNDetailsByReferenceNumber>
  </soap:Body>
</soap:Envelope>

Response Code

HTTP/1.1 200 OKContent-Type: text/xml; charset=utf-8
Content-Length: length


<?xml version="1.0" encoding="utf-8"?>
<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/">
  <soap:Body>
    <GetCNDetailsByReferenceNumberResponse xmlns="http://202.61.51.93:6265/">
      <GetCNDetailsByReferenceNumberResult>xmlxml</GetCNDetailsByReferenceNumberResult>
    </GetCNDetailsByReferenceNumberResponse>
  </soap:Body>
</soap:Envelope>

By running above code on Excel 2016 Windows 10 I get User Defined Type not defined

https://prnt.sc/jpd236

and how we can run this code in Google Sheet any one can help please

 

[ghanchi2000]

Please see request and response code here

https://prnt.sc/jpd339

 

[ghanchi2000]

Now I am getting this error
https://prnt.sc/jpdw15