Call out to web service from Excel 2010

Question

Starting an innovation week at work here. Good ol' Excel project! So here's what we want. We want to be able to create user defined functions in Excel, just like Excel has the SUM, etc. we want to make our own.

These UDF's would take Excel data in, and they would then pass that data to a Web Service which would do what it needs to do and then return the data back. I know how the Web Services will act, but what I need some guidance on is preparing the data in Excel, setting up the UDF's, and dealing with the sending/receiving of the data in Excel. I've never done any Excel programming before.

At first glance, it seems I might not need to do any VBA work, and that I could almost create a plugin for Excel (generate a .xll file from C#), and then go about it this way, but I'm not really sure how this all works.

Am I approaching this right? Are there any other things I should keep in mind? Is there anything I should read up on (any good links)?

Thanks.

Answer 1

You could take a look at XLLoop. This is a framework for developing Excel UDFs on a remote server. It has server implementations in a range of languages and is open source with a liberal license.

(Full disclosure: I work on this project).

Answer 2

There are good articles like below on MSDN.

Article 1 Article 2

Videos on Youtube and other Video 1 Video 2

Answer 3

You can call a WCF service from Excel with VSTO or with VBA

Answer 4

You will want to use VSTO to create an add-on for Excel. Using Visual Studio create a new project. Select Office and then Excel 2007 Add-on or Excel 2010 Add-on. Check out this great resource for examples on how to get started http://www.packtpub.com/article/microsoft-office-excel-programming-using-vsto it pretty much has everything you are asking for (except calling a web service).

It may be possible to do all of this in VBA but it won't be as easy and you'll have to deal with the security issues that come with it.