I'm trying to make a macro in VBA in excel to send a request through a Web Service SOAP service, I managed to create it with the following code confirming that it actually works:
Sub erp_automatico()
Dim sURL As String
Dim sEnv As String
Dim xmlhtp As MSXML2.XMLHTTP60
Dim xmldoc As MSXML2.DOMDocument60
sURL = "https://b2b.falabella.com/b2bwsfacopr/ws/adminArchivoService"
sEnv = "<soapenv:Envelope
xmlns:soapenv=""http://schemas.xmlsoap.org/soap/envelope/""
xmlns:adm=""http://b2b.falabella.com/schemas/ol/admin-archivo-carga-
request"">"
sEnv = sEnv & " <soapenv:Header><wsse:Security
soapenv:mustUnderstand=""1"" xmlns:wsse=""http://docs.oasis-
open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd"">
<wsse:UsernameToken wsu:Id=""UsernameToken-37""
xmlns:wsu=""http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd"">"
sEnv = sEnv & " <wsse:Username>811111111-0|33333333-0</wsse:Username>"
sEnv = sEnv & " <wsse:Password Type=""http://docs.oasis- open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText"">79521984yusidkfgj21F60A7B5</wsse:Password>"
sEnv = sEnv & " <wsse:Nonce EncodingType=""http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-soap-message-security-1.0#Base64Binary"">/ZM4HrHD7nAQFQd+dwvdbw==</wsse:Nonce><wsu:Created>2017-12-11T17:49:15.362Z</wsu:Created></wsse:UsernameToken></wsse:Security></soapenv:Header>"
sEnv = sEnv & " <soapenv:Body>"
sEnv = sEnv & " <adm:admin-archivo-carga-request>"
sEnv = sEnv & " <adm:files>"
sEnv = sEnv & " <!--1 or more repetitions:-->"
sEnv = sEnv & " <adm:file>"
sEnv = sEnv & " <adm:tipo-archivo>eRP</adm:tipo-archivo>"
sEnv = sEnv & " <adm:archivo>gjhhgfjg</adm:archivo>"
sEnv = sEnv & " <adm:extension-archivo>XML</adm:extension-archivo>"
sEnv = sEnv & " </adm:file>"
sEnv = sEnv & " </adm:files>"
sEnv = sEnv & " </adm:admin-archivo-carga-request>"
sEnv = sEnv & " </soapenv:Body>"
sEnv = sEnv & "</soapenv:Envelope>"
Set xmlhtp = New MSXML2.XMLHTTP60
With xmlhtp
.Open "POST", sURL, False
.setRequestHeader "Content-Type", "text/xml;charset=UTF-8"
.setRequestHeader "SOAPAction", ""
.setRequestHeader "Content-Length", "Length"
.send sEnv
Set xmldoc = New MSXML2.DOMDocument60
xmldoc.LoadXML .responseText
Debug.Print xmldoc.XML
End With
Set xmldoc = Nothing
Set xmlhtp = Nothing
End Sub
Now, in the <adm:archivo> tag of the service, I must send an XML text like the following:
<![CDATA[<eRP><ACCION>RP</ACCION><PROVEEDOR>890101279</PROVEEDOR><USUARIO>11111111</USUARIO><RP num="1"><FOLIO>12539296670</FOLIO><GUIA>428385200</GUIA><FACTURA/><CANT_BULTOS>1</CANT_BULTOS><PRODUCTO><PRODUCTO_ROW num="1"><SKU>3470950</SKU><ETIQUETA>428385200</ETIQUETA></PRODUCTO_ROW></PRODUCTO></RP></eRP>]]>
but when I put it inside the tag, excel throws me a compilation error.
Performing the same action in SoapUI the service responds correctly.
I appreciate the help you can give me.