• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Run a SOAP statement thru an excel VBA macro

Nirbhay Sharma

New Member
Hi All,

I want to implement a macro, that can run a SOAP statement (present in the file itself), and send the values present in the excel file to a particular system.
Let me break it down for you:

1) there is an excel file with data on something...say Sales Calls made by a Sales Rep
2) there is a pre-tested SOAP statement that will be used to load the data inside the excel using a webservice and a username password authentication, and one by one run the soap statement for all the records.

I am new to SOAP but managed to learn it enough to get a SOAP request running for a record manually in SOAP UI. Now it needs to be automated using excel VBA macros. Any help would be welcome. Even a basic structure or a different example of a SOAP running thru a macro dynamically picking values from excel cells, it would be great as i can extend it.

Plz have a look at the sample.

Thanks in advance :)
 

Attachments

  • Sample SOAP Macro.xlsx
    10.7 KB · Views: 137
If you have access to REST web services it may be easier than SOAP method, if you are passing from Excel to web services.

Some links:
http://blog.smartbear.com/apis/understanding-soap-and-rest-basics/
http://libkod.info/officexml-CHP-9-SECT-5.shtml

If you are set on SOAP. Groovy script may be easier than VBA. So instead of using VBA to pass info to web service, you use Groovy to read Excel and pass info to web service.

Reference link:
http://stackoverflow.com/questions/22031375/reading-excel-and-writing-to-xml-in-groovy-for-soapui
 
Hi Chihiro
thanks for the reply, but the issue is that i have to implement this within excel itself. i cannot use any external software/program for the same.

is there any help you can provide within excel itself?
 
I've only done a Response call from SOAP via VBA.
But you will need to reference Microsoft XML, v6.0 Tools in VBA.

Below is the link to codes I referenced back few years ago:
http://www.vbaexpress.com/forum/showthread.php?39408-SOAP-Calls-In-Excel-2007-2010
http://scn.sap.com/community/epm/bl...nvoke-a-soap-web-service-from-custom-vba-code

Check if your web service supports REST method. It's much simpler to use as it's string based interface instead of object oriented approach SOAP uses.

Alternately you should be able to generate XML from Excel sheet to post to web service.
http://libkod.info/officexml-CHP-6-SECT-4.shtml
 
unfortunately the webservice doesnt support REST. it is only SOAP based. thus we have to use that only.

i am looking at the process wherein we can generate the xml first then send it to webservice. trying that out.

but i am not quite sure how we will send it to web service using excel after the generation of the XML using macros. any suggestions for that?

using this link for generating xml:
http://libkod.info/officexml-CHP-6-SECT-4.shtml

thanks for you help :)
 
hi chihiro

thanks a lot for your help.
i was able to create an xml and also send it thru to the webservices.
i will share the code after a bit of modifications (removing the password and stuff).

thanks a lot once again :)
 
hi chihiro

thanks a lot for your help.
i was able to create an xml and also send it thru to the webservices.
i will share the code after a bit of modifications (removing the password and stuff).

thanks a lot once again :)

I have exactly the same issue I am trying to solve. Are you able to post your solution?

Thanks,
 
Hi,
I am looking for same solution, unfortunately the links
http://www.dvwsolutions.com/blog/en...-big-investment-of-time-money-and-effort.html
and
https://www.scribd.com/document/332571110/How-to-Upload-Data-From-MS-Excel-Directly-Into-SAP-BW
both are broken. Request you to reshare the knowledge.
Thanks and Regards,
Ashok
hi chihiro

thanks a lot for your help.
i was able to create an xml and also send it thru to the webservices.
i will share the code after a bit of modifications (removing the password and stuff).

thanks a lot once again :)
 
Hi Chihiro,
Thank you for your response, I did try unable to find the url. I can understand its a while now, do you still have the file/code yet?
Any help will be very useful.

BR,
Ashok
 
If you read my post, you'll see that I don't work with SOAP via VBA. Almost all modern sites works with RESTful API.
I personally hate working with XML... and when I must, I usually use python (which has more robust error handling and easier to work using IDE) to construct xml and Post to web service.

In case of SOAP, just as you would Post query string to RESTful API, you have to generate XML string/file and Post that to SOAP API.

And as I've stated. Use Web Archive and look for crawl history to find the article that is gone.
https://web.archive.org/

Ex:
https://web.archive.org/web/2015053...-big-investment-of-time-money-and-effort.html

Also if you search using "SOAP VBA" etc, you'll find plenty of examples.
 
Last edited:
Thank you so much, I tried the same archive site but did not get any response, but somehow this link is working,
I will try and let you know the results
 
Back
Top