What is the best way of accessing a WCF service from Excel?

1
votes

I would like to access a WCF service through VBA functions/macros in Excel (2007 or 2010).

It seems there are a number of possibilities, each with its own particular shortcomings. ...

  • Microsoft SOAP Toolkit
  • WCF Service Moniker
  • COM Interop
  • VSTO
  • Excel-DNA

Can anyone advise on the best way of doing this?

1
wcfexcelvbaservicevsto
Can you share a bit how you would like to use the service / what you want to do with it? – Mathias
The service exposes an API who's primary purpose is to extract data from a database. I would like to create VBA functions/macros that can pull this data into Excel. – TomC

1 Answers

0
votes

I don't think there is a clear-cut answer to your question; it depends a bit on what you want to do with the data returned from the service, how you intend to deploy your solution, and how much is done with VBA, as opposed to .NET.
My gut feeling is that VSTO is probably right, because it gives you a full-fledged .NET project, which will likely be the most convenient to handle the WCF services. Assuming that what you are trying to do is retrieve data and give your user choices as to what to pull and how to display it, you can then build a user interface for it (maybe in a task pane), and write the results to Excel, while writing code in Visual Studio.
However, you mentioned VBA, and I am not quite sure how much you want to use it. I found ExcelDNA to be easier to use if what you want to do is create a VBA user-defined function which calls a .NET dll. If VBA is what you want to focus on, this may be the way to go.
Hope this helps!