CDATA with values from the active worksheet
Hi everyone,
My code for calling a SOAP webservice and inserting data works like a charm.
The next step to loop this code until the rows of the active worksheet ran out of data works as well, but my cdata has to be filled with the data of the cells.
I couldnt really found a way to do this. So i hope you know a easy and simple way to make this happen.
This is my code:
Code:
Option Explicit
Public Sub PingRN()
Dim xmlHtp As New MSXML2.XMLHTTP60
Dim sURL As String
Dim sEnv As String
Dim XMLDOC As New DOMDocument60
Dim onderwerp As Range
Dim nummer As Range
Dim bestand As Range
Dim i As Long
sURL = "webservice link"
For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
With ActiveSheet
Set onderwerp = Range("A" & i)
Set nummer = Range("C" & i)
Set bestand = Range("B" & i)
End With
sEnv = "<?xml version=""1.0"" encoding=""utf-8""?>"
sEnv = sEnv & "<soap:Envelope xmlns:soap=""w3 envelope link"" xmlns:urn=""urn:Afas.Profit.Services"">"
sEnv = sEnv & "<soap:Header/>"
sEnv = sEnv & "<soap:Body>"
sEnv = sEnv & "<urn:Execute>"
sEnv = sEnv & "<urn:environmentId>TSTOUTS</urn:environmentId>"
sEnv = sEnv & "<urn:userId>XXXX</urn:userId>"
sEnv = sEnv & "<urn:password>XXXX</urn:password>"
sEnv = sEnv & "<urn:connectorType>KnSubject</urn:connectorType>"
sEnv = sEnv & "<urn:connectorVersion>1</urn:connectorVersion>"
sEnv = sEnv & "<urn:dataXml><=!=[=C=D=A=T=A=[<KnSubject xmlns:xsi=""w3 schema instance link""><Element><Fields Action=""insert""><StId>86</StId><Ds> onderwerp.value </Ds><SbPa> ""c:\"" & bestand.value "".pdf"" </SbPa><FileTrans>True</FileTrans></Fields><Objects><KnSubjectLink><Element SbId=""""><Fields Action=""insert""><SfTp>2</SfTp><SfId> nummer.value </SfId><ToEM>True</ToEM></Fields></Element></KnSubjectLink></Objects></Element></KnSubject>]=]=></urn:dataXml>"
sEnv = sEnv & "</urn:Execute>"
sEnv = sEnv & "</soap:Body>"
sEnv = sEnv & "</soap:Envelope>"
With xmlHtp
.Open "POST", sURL, False
.setRequestHeader "Accept-encoding", "gzip, deflate"
.setRequestHeader "Host", "localhost"
.setRequestHeader "Content-Type", "application/soap+xml; charset=utf-8; "
.setRequestHeader "soapAction", "urn:Afas.Profit.Services/Execute"
.setRequestHeader "User-Agent", "Apache-HttpClient/4.1.1 (java 1.5)"
.send sEnv
XMLDOC.LoadXML .responseText
XMLDOC.Save ActiveWorkbook.Path & "\WebQueryResult2.xml"
End With
Next i
MsgBox xmlHtp.statusText
End Sub
The variable values in CDATA are: bestand, nummer and onderwerp.
I hope you guys have a simple and easy way to help me.
Thanks in advance.
