Using excel to connect to ERP SOAP webservice

[krentenbol]

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

[snb]

Did you explore the import facilities in the Developers Tab in Excel ?

[krentenbol]

Yes i have been looking through this tab, but couldnt find anything helpful for my cause.
Back in the day there was this toolkit called: Webservices toolkit 2003.
This seemed to do the trick, but this isnt available anymore in office 2013.

I just have to create this macro that sends data to the webservice. The data will be from the active excel sheet and the macro will continue looping until the end of the column.

I just cant figure out how to create this in vba.

I will just keep on trying :)

Thanks anyway

[Kenneth Hobs]

See if these help.

http://www.vbaexpress.com/forum/showthread.php?p=252560
http://www.vbaexpress.com/forum/showthread.php?t=34354

[krentenbol]

Thanks for replying.


I got it practically working. The server however generates a error. This error isn't very specific and I wonder If I have done anything wrong with syntax?
I was thinking that it might had something to do with: Dim xmlhtp As New MSXML2.XMLHTTP40, since this is a request statement?
This is the code:


Option Explicit
Sub DoIt()
Dim sURL As String
Dim sEnv As String
Dim xmlhtp As New MSXML2.XMLHTTP40
sURL = "webservice link "
sEnv = "<?xml version=""1.0"" encoding=""utf-8""?>"
sEnv = sEnv & " <soap:Envelope xmlns:xsi=""w3c link"" xmlns:xsd=""w3c link"" xmlns:soap=""soap link"">"
sEnv = sEnv & " <soap:Body>"
sEnv = sEnv & " <Execute xmlns=""urn:Afas.Profit.Services"">"
sEnv = sEnv & " <environmentId>TSTOUTS</environmentId>"
sEnv = sEnv & " <userId>string</userId>"
sEnv = sEnv & " <password>string</password>"
sEnv = sEnv & " <logonAs></logonAs>"
sEnv = sEnv & " <connectorType>KnSubject</connectorType>"
sEnv = sEnv & " <connectorVersion>1</connectorVersion>"
sEnv = sEnv & " <dataXml><![CDATA[<KnSubject xmlns:xsi=""w3c link "" > <Element> <Fields Action=""insert""><StId>86</StId><Ds>test uit excel</Ds><SbPa>C:\test.pdf</SbPa><FileTrans>True</FileTrans></Fields>-<Objects>-<KnSubjectLink>-<Element SbId="">-<Fields Action=""insert""><SfTp>2</SfTp><SfId>12345</SfId><ToEM>True</ToEM></Fields></Element></KnSubjectLink></Objects></Element></KnSubject>]]> </dataXml>"
sEnv = sEnv & " </Execute>"
sEnv = sEnv & " </soap:Body>"
sEnv = sEnv & " </soap:Envelope>"
With xmlhtp
.Open "post", sURL, False
.setRequestHeader "Host", "profit02"
.setRequestHeader "Content-Type", "text/xml; charset=utf-8"
.setRequestHeader "Content-Length", "length"
.setRequestHeader "soapAction", "urn:Afas.Profit.Services/Execute"
.send sEnv
MsgBox .responseText
End With
End Sub



Thanks in advance

*note that I changed actual links to something like: w3c link, because I cannot post links yet.

[Kenneth Hobs]

See the examples that I referenced. If you can reference objects without their version numbers you will be better off.

[krentenbol]

Hey.

It is almost working! The only part that is still a issue is my CDATA tag within dataXml.
I know that the parser is supposed to leave anything within a CDATA tag, but mine still requires me to put quotes etc.. within the data of CDATA.

Is there anything i can do to avoid this?

Here is the code that should be within the CDATA:


<KnSubject xmlns:xsi="w3c-link"><Element><Fields Action="insert"><StId>86</StId><Ds>Dossieritem met bijlage</Ds><SbPa>C:\Users\vve\Downloads\testxml.xml</SbPa><FileTrans>True</FileTrans></Fields><Objects><KnSubjectLink><Element SbId=""><Fields Action="insert"><SfTp>2</SfTp><SfId>xxxxx</SfId><ToEM>True</ToEM></Fields></Element></KnSubjectLink></Objects></Element></KnSubject>

This is how its now: (note the extra quotes at weblinks and insert)


<![CDATA[<KnSubject xmlns:xsi=""w3c-link""><Element><Fields Action=""insert""><StId>86</StId><Ds>Dossieritem met bijlage</Ds><SbPa>C:\Users\vve\Downloads\testpdf</SbPa><FileTrans>True</FileTrans></Fields><Objects><KnSubjectLink><Element SbId=""><Fields Action=""insert""><SfTp>2</SfTp><SfId>xxxxxx</SfId><ToEM>True</ToEM></Fields></Element></KnSubjectLink></Objects></Element></KnSubject>]]>

Thanks in advance

[Kenneth Hobs]

If you need to send a quote character, double quotes in a string is one way. That method can be a bit confusing sometimes. I like to build a string using the concatenating operator &. I set q="""" and then build the string and concatenate q when needed. This makes debugging easier. Whatever method works best for you, then use it.

[snb]

<Element SbId="">

should be:

<Element SbId="""">

[krentenbol]

MY HERO!!!

I really spend hours, figuring out what was wrong... And all that it took was 2 quotes haha
When i copy pasted the line into vba, it gave errors on the weblinks and insert`s, but not at SbId..

Thanks very much!

[Marius Titul]

Works like a charm. Thanks for the tips, guys!