CDATA with values from the active worksheet

[krentenbol]

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:

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.

[Bob Phillips]

Do you just need to change this

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>"

to this

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>"

[krentenbol]

Thanks! it works great now.