Invoke a SOAP Web Service from Excel VBA

[DD646]

Hi,

I am trying to call a SOAP Webservice from Excel VBA.

I have a dummy service to just add 2 Integers that I found from the Internet.

Can anyone help me with the code?

http://www.dneonline.com/calculator.asmx?op=Add

[DD646]

Any help??

[Kyle123]

You just post the xml to the required url, it requires quite a bit of VBA coding though. Is this something you want to know how to do out of curiosity or are you trying to call a SOAP service that's not the one you've linked to? The reason I ask is that whilst the principles are similar, you won't be able to easily adapt sample code to your actual requirements without a good knowledge of VBA

[DD646]

Hi,

I actually use a code which I got from Excel Forum which I think was posted by you.

I have changed a bit of it to suit my requirements.

However, this is a Restful Service. When I try using the same for a SOAP Service it does not work.

Please Login or Register  to view this content.

[Kyle123]

Did you even read my answer?

[DD646]

Hi Kyle,

Yes I did. Actually I have decent knowledge of VBA and If you help me out with a VBA Code for the service I have linked to, I will be able to find my way for my actual service.

Thanks

[DD646]

Hi Sir,

I have given it a try, but I am stuck up..

The code does generate a response, but something wierd..

Any inputs will be appreciated.

It is blocking me when I post the code, so attaching a screenshot

[Kyle123]

Working example, you probably don't want to use string building/parsing for anything more indepth:

https://pastebin.com/raw/1D80cuZj

[DD646]

Thank You so much Kyle..

Works as desired.

[DD646]

I just had 1 query if you could help me clarify.

You kept the SOAP Envelope as it is on the Webservice, but just changed the soap to soap12. Can you tell me why was that?

Thank You

[Kyle123]

I used SOAP 1.2 it's the second method on the page you linked to

[Doc.AElstein]

@ DD646, and Kyle
Regarding the Sucuri Website Firewall blocking… see this Thread, - https://www.excelforum.com/suggestio...ml#post4736073 – your problem is probably things like

<soap
<intA

Etc…..

Unfortunately Sucuri Website Firewall blocks things like that.

Work arounds to get it to post are discussed in that above thread. ( I used the “black character trick” to get them to post here)

Alan

[DD646]

@Doc.AElstein

Thank you very much for the clarification

[Doc.AElstein]

You is welcome,

<Thanks <for <the <feed <back
'_- posted using black character trick on <Thanks , <feed , and <back as
<Th[COLOR="#000000"]an[/COLOR]ks <for <the <f[COLOR=black]e[/COLOR]ed <bac[COLOR="#000000"]k[/COLOR]

Or
'_- just use the space character trick on all words like
<T hanks <for <the < feed < back
(Note all those words use the space trick , even the <for and <the which have the 4th character as a space character. ( The first 4 characters after the < need to be considered ) )

Alan

[Kyle123]

Or you could just stick it in paste bin

[DD646]

Hi Kyle,

With your help, I was successfully able to call a few different trial services.

However, this 1 service where I am having issues with parsing the data.

Here is my code which generates a response sucessfully

https://pastebin.com/raw/dQ051PLL

Here is the webservice link -

http://www.webservicex.net/globalwea...itiesByCountry

Can you help me a bit with Parsing the response?

Cheers

[arunac]

I am trying the same with a wsdl that has the following structure , when i use the same code snippet, I get the error"no SOAPAction header". I suspect it has something to do with setting a header as given in code snippet.
This is the Code line being used. The variable L_XML has the xml as a string obtained from a file.

Please Login or Register  to view this content.

[arunac]

This is my SOAP request xml

Please Login or Register  to view this content.

[arunac]

This is the response xml

Please Login or Register  to view this content.

[FDibbins]

I am trying the same with a wsdl that has the following structure , when i use the same code snippet, I get the error"no SOAPAction header". I suspect it has something to do with setting a header as given in code snippet.
This is the Code line being used. The variable L_XML has the xml as a string obtained from a file....

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

[arunac]

Sure Ford. Thanks.I will create a new thread for my query. I am not able to delete my posts here though