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></StatePro vince>"
sEnv = sEnv & "<PostalCode></PostalCode> "
sEnv = sEnv & "<AddressLine1></AddressLi ne1>"
sEnv = sEnv & "<AddressLine2></AddressLi ne2>"
sEnv = sEnv & "<County></County>"
sEnv = sEnv & "<AddressLine3></AddressLi ne3>"
sEnv = sEnv & "<RecordID>1</RecordID>"
sEnv = sEnv & "<user_fields></user_field s>"
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></Clean sed_Addres s>"
sEnv = sEnv & "<City></City>"
sEnv = sEnv & "<County></County>"
sEnv = sEnv & "<PostalCode></PostalCode> "
sEnv = sEnv & "<StateProvince></StatePro vince>"
sEnv = sEnv & "<UniqueID></UniqueID>"
sEnv = sEnv & "<Country.ISO>UK</Country. ISO>"
sEnv = sEnv & "<SI_L1_Country></SI_L1_Co untry>"
sEnv = sEnv & "<SI_L2_State></SI_L2_Stat e>"
sEnv = sEnv & "<SI_L3_County></SI_L3_Cou nty>"
sEnv = sEnv & "<SI_L4_City></SI_L4_City> "
sEnv = sEnv & "<SI_L5_Postcode></SI_L5_P ostcode>"
sEnv = sEnv & "<PBKey></PBKey>"
sEnv = sEnv & "<Status></Status>"
sEnv = sEnv & "<Status.Code></Status.Cod e>"
sEnv = sEnv & "<Status.Description></Sta tus.Descri ption>"
sEnv = sEnv & "<SI_L4_City></SI_L4_City> "
sEnv = sEnv & "<user_fields></user_field s>"
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 :)
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></StatePro
sEnv = sEnv & "<PostalCode></PostalCode>
sEnv = sEnv & "<AddressLine1></AddressLi
sEnv = sEnv & "<AddressLine2></AddressLi
sEnv = sEnv & "<County></County>"
sEnv = sEnv & "<AddressLine3></AddressLi
sEnv = sEnv & "<RecordID>1</RecordID>"
sEnv = sEnv & "<user_fields></user_field
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></Clean
sEnv = sEnv & "<City></City>"
sEnv = sEnv & "<County></County>"
sEnv = sEnv & "<PostalCode></PostalCode>
sEnv = sEnv & "<StateProvince></StatePro
sEnv = sEnv & "<UniqueID></UniqueID>"
sEnv = sEnv & "<Country.ISO>UK</Country.
sEnv = sEnv & "<SI_L1_Country></SI_L1_Co
sEnv = sEnv & "<SI_L2_State></SI_L2_Stat
sEnv = sEnv & "<SI_L3_County></SI_L3_Cou
sEnv = sEnv & "<SI_L4_City></SI_L4_City>
sEnv = sEnv & "<SI_L5_Postcode></SI_L5_P
sEnv = sEnv & "<PBKey></PBKey>"
sEnv = sEnv & "<Status></Status>"
sEnv = sEnv & "<Status.Code></Status.Cod
sEnv = sEnv & "<Status.Description></Sta
sEnv = sEnv & "<SI_L4_City></SI_L4_City>
sEnv = sEnv & "<user_fields></user_field
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 TRIALMembers 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.