Link to home
Start Free TrialLog in
Avatar of Anirudh Pandey
Anirudh Pandey

asked on

How to authenticate API webservice using VBA and SOAP?

I am unable to authenticate to webservice with my VBA code.

Below is the code :-

Sub Test_GetQuote() Dim s As String s = VRLogin() MsgBox s End Sub

Function VRLogin() As String

Dim sURL As String Dim sEnv As String Dim xmlhtp As New MSXML2.ServerXMLHTTP60 ' enter below web service url sURL = ""

sEnv = "<?xml version =""1.0"" encoding=""utf-8""?>"
sEnv = sEnv & "<soap:Envelop xmlns:ns1=""http://www.g1.com/services/WorldGeocoder"">"
sEnv = sEnv & "<soap:Envelop xmlns:ns2=""http://cxf.apache.org/bindings/xformat"">"
sEnv = sEnv & "<soap:Envelop xmlns:soap=""http://schemas.xmlsoap.org/wsdl/soap/"">"
sEnv = sEnv & "<soap:Envelop xmlns:tns=""http://www.g1.com/"">"
sEnv = sEnv & "<soap:Envelop xmlns:wsdl=""http://schemas.xmlsoap.org/wsdl/"">"
sEnv = sEnv & "<soap:Envelop xmlns:xsd=""http://www.w3.org/2001/XMLSchema"">"
sEnv = sEnv & "<soap:Envelop targetNamespace=""http://www.g1.com/"">"

sEnv = sEnv & "<soap:Body>"

sEnv = sEnv & "<keyByAddressRequest>"   'Request to Web service

    sEnv = sEnv & "<address>"

    sEnv = sEnv & "<Country></Country>"
    sEnv = sEnv & "<City></City>"
    sEnv = sEnv & "<StateProvince></StateProvince>"
    sEnv = sEnv & "<PostalCode></PostalCode>"
    sEnv = sEnv & "<AddressLine1></AddressLine1>"
    sEnv = sEnv & "<AddressLine2></AddressLine2>"
    sEnv = sEnv & "<County></County>"
    sEnv = sEnv & "<AddressLine3></AddressLine3>"
    sEnv = sEnv & "<RecordID>1</RecordID>"
    sEnv = sEnv & "<user_fields></user_fields>"

    sEnv = sEnv & "</address>"

sEnv = sEnv & "</keyByAddressRequest>"

sEnv = sEnv & "<keyByAddressResponse>"  'Response from Web service

    sEnv = sEnv & "<address>"

    sEnv = sEnv & "<Resolution></Resolution>"
    sEnv = sEnv & "<Latitude></Latitude>"
    sEnv = sEnv & "<Longitude></Longitude>"
    sEnv = sEnv & "<Cleansed_Address></Cleansed_Address>"
    sEnv = sEnv & "<City></City>"
    sEnv = sEnv & "<County></County>"

    sEnv = sEnv & "<PostalCode></PostalCode>"
    sEnv = sEnv & "<StateProvince></StateProvince>"
    sEnv = sEnv & "<UniqueID></UniqueID>"
    sEnv = sEnv & "<Country.ISO>UK</Country.ISO>"
    sEnv = sEnv & "<SI_L1_Country></SI_L1_Country>"
    sEnv = sEnv & "<SI_L2_State></SI_L2_State>"
    sEnv = sEnv & "<SI_L3_County></SI_L3_County>"
    sEnv = sEnv & "<SI_L4_City></SI_L4_City>"
    sEnv = sEnv & "<SI_L5_Postcode></SI_L5_Postcode>"
    sEnv = sEnv & "<PBKey></PBKey>"
    sEnv = sEnv & "<Status></Status>"
    sEnv = sEnv & "<Status.Code></Status.Code>"
    sEnv = sEnv & "<Status.Description></Status.Description>"
    sEnv = sEnv & "<SI_L4_City></SI_L4_City>"
    sEnv = sEnv & "<user_fields></user_fields>"

    sEnv = sEnv & "</address>"

sEnv = sEnv & "</keyByAddressResponse>"

sEnv = sEnv & "    </soap:Body>"
sEnv = sEnv & "</soap:Envelope>"
xmlhtp.Open "POST", sURL, False, "", "" 'Authentication xmlhtp.setRequestHeader "Content-Type", "text/xml"

xmlhtp.setRequestHeader "SOAPAction", "http://something?wsdl" 'SOAP xmlhtp.send sEnv

VRLogin = xmlhtp.responseText

End Function

I get below error when i run this code :-

soap:ClientError reading XMLStreamReader.

any help would be greatly appreciated :)
This question needs an answer!
Become an EE member today
7 DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.