krentenbol
12-16-2013, 03:50 AM
Hi all,
My company`s ERP system uses a SOAP webservice to add data from external applications.
I am now trying to get my XML code to work in a Excel macro, so we can add data to our ERP system with data from a excel worksheet.
Using SoapUI, I was able to use this code to connect and import data:
<soap:Envelope xmlns:soap= "{link}" xmlns:urn="urn:Afas.Profit.Services">
<soap:Header/>
<soap:Body>
<urn:Execute>
<urn:environmentId>TSTOUTS</urn:environmentId>
<urn:userId>[string]</urn:userId>
<urn:password>[string]</urn:password>
<urn:connectorType>KnSubject</urn:connectorType>
<urn:connectorVersion>1</urn:connectorVersion>
<urn:dataXml><
![CDATA[<KnSubject xmlns:xsi="{link}"><Element> <Fields Action="insert"><StId>86</StId><Ds> {excel variable} </Ds><SbPa> {excel worksheet variable} </SbPa><FileTrans>True</FileTrans></Fields><Objects><KnSubjectLink><Element SbId="">-<Fields Action="insert"><SfTp>2</SfTp><SfId> {excel variable} </SfId><ToEM>True</ToEM></Fields></Element></KnSubjectLink></Objects></Element></KnSubject>]=]=>
</urn:dataXml>
</urn:Execute>
</soap:Body>
</soap:Envelope>
Is it possible to convert this code suitable for a macro in excel?
And is so, is it possible to create a loop that keeps importing data from the excel worksheet until it reaches the end?
I have been fiddling around some time now to get this thing working, but my knowledge in VBA is limited and searching on internet doesn't seem to do the trick.
EDIT:
Forgot to post the progress I have made on my own:
Sub Macro1()
Set objHTTP = New MSXML.XMLHTTPRequest
objHTTP.Open "POST", "[webservice link]"
objHTTP.SetRequestHeader "Content-Type", "text/xml"
? Content -Length: Length ?
Dim sEnv As String
sEnv = "<?xml version=""1.0"" encoding=""utf-8""?>"
sEnv = sEnv & "<soap12:Envelope xmlns:xsi=""[w3c link]"">"
sEnv = sEnv & "<soap12:Body>"
sEnv = sEnv & "<Execute xmlns=urn:Afas.Profit.Services>"
sEnv = sEnv & "<urn:environmentId>TSTOUTS</urn:environmentId>"
sEnv = sEnv & "<urn:userId>'string'</urn:userId>"
sEnv = sEnv & "<urn:password>'string'</urn:password>"
sEnv = sEnv & "<urn:connectorType>KnSubject</urn:connectorType>"
sEnv = sEnv & "<urn:connectorVersion>1</urn:connectorVersion><urn:dataXml>"
sEnv = sEnv & "<urn:dataXml><![CDATA..[[]><urn:/dataXml>"
sEnv = sEnv & "</Execute>"
sEnv = sEnv & "</soap12:Body>"
sEnv = sEnv & "</soap12:Envelope>"
End Sub
This code is above is based on the "example" request when browsing the webservice:
POST webservice.link HTTP/1.1
Host: servername
Content-Type: application/soap+xml; charset=utf-8
Content-Length: length
<?xml version="1.0" encoding="utf-8"?>
<soap12:Envelope xmlns:xsi="w3c link" xmlns:xsd="w3c link" xmlns:soap12="w3c link">
<soap12:Body>
<Execute xmlns="urn:Afas.Profit.Services">
<environmentId>string</environmentId>
<userId>string</userId>
<password>string</password>
<logonAs>string</logonAs>
<connectorType>string</connectorType>
<connectorVersion>int</connectorVersion>
<dataXml> <![CDATA..[[]></dataXml>
</Execute>
</soap12:Body>
</soap12:Envelope>
Thanks in advance,
Vincent
My company`s ERP system uses a SOAP webservice to add data from external applications.
I am now trying to get my XML code to work in a Excel macro, so we can add data to our ERP system with data from a excel worksheet.
Using SoapUI, I was able to use this code to connect and import data:
<soap:Envelope xmlns:soap= "{link}" xmlns:urn="urn:Afas.Profit.Services">
<soap:Header/>
<soap:Body>
<urn:Execute>
<urn:environmentId>TSTOUTS</urn:environmentId>
<urn:userId>[string]</urn:userId>
<urn:password>[string]</urn:password>
<urn:connectorType>KnSubject</urn:connectorType>
<urn:connectorVersion>1</urn:connectorVersion>
<urn:dataXml><
![CDATA[<KnSubject xmlns:xsi="{link}"><Element> <Fields Action="insert"><StId>86</StId><Ds> {excel variable} </Ds><SbPa> {excel worksheet variable} </SbPa><FileTrans>True</FileTrans></Fields><Objects><KnSubjectLink><Element SbId="">-<Fields Action="insert"><SfTp>2</SfTp><SfId> {excel variable} </SfId><ToEM>True</ToEM></Fields></Element></KnSubjectLink></Objects></Element></KnSubject>]=]=>
</urn:dataXml>
</urn:Execute>
</soap:Body>
</soap:Envelope>
Is it possible to convert this code suitable for a macro in excel?
And is so, is it possible to create a loop that keeps importing data from the excel worksheet until it reaches the end?
I have been fiddling around some time now to get this thing working, but my knowledge in VBA is limited and searching on internet doesn't seem to do the trick.
EDIT:
Forgot to post the progress I have made on my own:
Sub Macro1()
Set objHTTP = New MSXML.XMLHTTPRequest
objHTTP.Open "POST", "[webservice link]"
objHTTP.SetRequestHeader "Content-Type", "text/xml"
? Content -Length: Length ?
Dim sEnv As String
sEnv = "<?xml version=""1.0"" encoding=""utf-8""?>"
sEnv = sEnv & "<soap12:Envelope xmlns:xsi=""[w3c link]"">"
sEnv = sEnv & "<soap12:Body>"
sEnv = sEnv & "<Execute xmlns=urn:Afas.Profit.Services>"
sEnv = sEnv & "<urn:environmentId>TSTOUTS</urn:environmentId>"
sEnv = sEnv & "<urn:userId>'string'</urn:userId>"
sEnv = sEnv & "<urn:password>'string'</urn:password>"
sEnv = sEnv & "<urn:connectorType>KnSubject</urn:connectorType>"
sEnv = sEnv & "<urn:connectorVersion>1</urn:connectorVersion><urn:dataXml>"
sEnv = sEnv & "<urn:dataXml><![CDATA..[[]><urn:/dataXml>"
sEnv = sEnv & "</Execute>"
sEnv = sEnv & "</soap12:Body>"
sEnv = sEnv & "</soap12:Envelope>"
End Sub
This code is above is based on the "example" request when browsing the webservice:
POST webservice.link HTTP/1.1
Host: servername
Content-Type: application/soap+xml; charset=utf-8
Content-Length: length
<?xml version="1.0" encoding="utf-8"?>
<soap12:Envelope xmlns:xsi="w3c link" xmlns:xsd="w3c link" xmlns:soap12="w3c link">
<soap12:Body>
<Execute xmlns="urn:Afas.Profit.Services">
<environmentId>string</environmentId>
<userId>string</userId>
<password>string</password>
<logonAs>string</logonAs>
<connectorType>string</connectorType>
<connectorVersion>int</connectorVersion>
<dataXml> <![CDATA..[[]></dataXml>
</Execute>
</soap12:Body>
</soap12:Envelope>
Thanks in advance,
Vincent