Calling web services from Excel
On of my customers uses a big Excel sheet for assessing business cases. It is used company wide in several countries by dozens of users. At the end the sheet calculates some key figures that then have to be entered into their portfolio management solution (which my company provided).
Currently they are transfering these 10+ key figures manually. When asked if I could help them to automate the process I said, easy. We just need to enhance the Excel sheet with some VBA code that calls a (WCF) web service in our application and make the whole thing a one click operation.
Everyone was happy and eager to see the new solution. But when I went back I started thinking about VBA calling SOAP services and quickly I found out that there is no built in way to do that.
VSTO to the rescue?
Well, actually there is a easy way to do this. The "official" way by Microsoft is to use VSTO. And yes, using VSTO let's you call the web service and it's easy to develop. Within minutes you can call the web service and get and update data. But is this really a workable solution? I don't think so.
The problem with VSTO is, that you end up with more than just an Excel file. You have your solution assembly, the config for the assembly where the WCF configuration resides (but which you could get rid off), the vsto file and the Excel VSTO assemblies.
Do you really want, or better can you safely distribute these files which each copy of the Excel sheet that is going to be created from the template? Probably not.
Even if you could do this in a small organization, you certainly can't do this in a global company, or when the workbook is going to be stored in SharePoint.
I have seen some other code on the web, where people tried to call the services with help of the MSXML library. But success was limited and worked only for some simple services using basic datatypes.
Excel-REST to the rescue
Luckily I stumbled across the Excel-REST library by Tim Hall. This library uses VBA code only and only has a dependency on the MS Scripting library. It uses some other libraries that it creates with the CreateObject command, but are available in the OS anyway.
Here the problem is, that it "only" calls REST services, but if you own the application, it is easy to add a REST wrapper around your web service. That is what I ended up doing. I used the ASP.NET Web API to create a REST interface which then called the web service internally.
Writing the code doing so was easy. When deploying to my own test server which uses Basic Http authentication over https it was no problem to add the authorization to the VBA code using the provided HttpBasicAuthenticator class.
I can only recommend to use the library as it allows you to call your services with minimal effort and leaves you with a single Excel file with VBA code that can easily be distributed.
