Recently I was looking at technologies for “Excel Addins” and come across “Excel DNA” (used by some of my colleagues). It is a free framework with a very permissive open-source license.

My need was to be able to consume a WCF service (.NET 3.0) from some VBA code in Excel 2007. Excel-DNA helped to make the final solution very easily. I did a prototype in little time, thanks to ExcelDNA (my Visual Studio 2008 solution sample RndExcelDna.zipRenamed, just renamed the file with a “.zip” extension).

Here is the overview of my prototype:

From the overview picture above, we have got:

• 2 host processes:
  • Excel.exe. It will host
    • The ExcelDna.xll plug-in (provided by ExcelDna framework).
    • The assembly MyWcfClient.dll, i.e. my add-in for Excel providing the UDF (User Defined Function) to be used by VBA in Excel.
    • The assembly MyWcfService.dll.
  • MyServiceHost.exe. It will host MyWcfService.dll containing my WCF service (contract definition + implementation).

Here, for simplicity, I packaged the WCF contract with its implementation in a single assembly, MyWcfService.dll . Usually, you will split the contract definition from the implementation, so deployment and distribution packages to client machines do not include the service code. In the end, the service will be executed and hosted by MyServiceHost.exe.

Regarding the WCF-proxy generated assembly (either by Visual Studio “Add Service Reference” feature or a tool such as svcutil.exe), the WCF proxy-boiled code is inside MyWcfClient.exe.

One key thing with .NET DLLs being hosted in a process is that the configuration file by default is the one of the host process, here “excel.exe.config” (if you installed Office 2007 using the default path, that file is located under “C:\Program Files\Microsoft Office\Office12”).  Excel-DNA will create an AppDomain to run your add-in and will configure it to look for “<add-in>.xll.config” file (See Govert’s comment further down – thank you). So, you have 3 options:

1. Edit Excel config file to have a section about the WCF client endpoint(s) that you want to consume.
2. Edit a config file with the name of your add-in (e.g. for my sample below, that would be HelloWcfAddin.xll.config)
3. Have another configuration file with the WCF information in it and load it yourself (you still need to know where this file is).  However, you will need to create the WCF objects programmatically (Endpoints, channels, …) using the custom configuration file (you will need to provide a name to your endpoint and use it in the constructor).

For simplicity for my prototype (at the time I was not aware of option 2), I used the former (edit “excel.exe.config):

I edited it and added the <system.serviceModel> section:

As documented by the ExcelDNA framework, I copied and renamed ExcelDna.xll to HelloWcfAddin.xll . I also added an xml file called HelloWcfAddin.dna (same name as for .xll file). The content of that file will have a reference to my add-in DLL, i.e. MyWcfClient.dll:

<DnaLibrary>
<ExternalLibrary Path=“MyWcfClient.dll“ />
</DnaLibrary>

I put the DLLs in a folder:

I double-clicked the HelloWcfAddin.xll. Excel was launched and I enabled this add-in. Here are a few screenshot from Excel if you ever have to enable/disable an Excel add-in:

In a cell, I tried my new UDF function, =GetMsgFromSvc(“Hello”) , and got an error: fine, at this stage I have not yet launch the service.

From a command line window, I launched my WCF service, MyServiceHost.exe:

I tried again the call from Excel; this time is was a success:

Here are the useful links about Excel-DNA I used to do my prototype:

• Excel-DNA page in Codeplex.
• a tutorial series by Ross McLean:
  • How does Excel DNA Work?
  • Writing an Excel DNA function Using an IDE
  • Excel DNA and the Excel Object Model