PDA

View Full Version : [SOLVED] CDATA with values from the active worksheet



krentenbol
12-30-2013, 08:20 AM
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
12-30-2013, 10:30 AM
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
12-31-2013, 03:27 AM
Thanks! it works great now.