How do I call WCF client from Excel 2003 VBA?

Question

Hi There,

As the question asked, how do I call WCF client from Excel 2003 VBA?

I saw there is a place where I can call web service, but I have searched through Google, all the results I get is not possible to call a WCF client from VBA.

I would like to know sort of what method to use before I do anything with my code, don't want to waste the time and discover later that it is not possible to do that.

Regards

Answer 1

I tried for quite a while to call a SOAP based wcf service from excel vba without any luck.

Instead I changed my service to REST binding (webHttpBinding). That way I could load the data stright into an xml map like it was any other xml file. Worked well for me, but I was only trying to import some data.

As for SOAP; an answer in this question mentions the Web Services Toolkit all the examples I could find suggested to use.

Answer 2

I have done this with limited success using the Office 2003 Web Services Toolkit, but not without some pain.

Note that this is not supported by Microsoft, and has a number of restrictions which may make it unsuitable for production use. E.g. it does not support web services that return an empty collections : see this StackOverflow question.

If you want to go this route despite it being unsupported, download the toolkit and post any problems you're having here.

Microsoft recommends using VSTO, but this doesn't integrate easily with VBA...

Answer 3

I wouldn't use VSTO in this case. COM Interop is probably the best way to go, so long as you don't want any security in your SOAP messages.

1) Write WCF client, with necessary binding 2) Expose assembly for COM Interop 3) Reference exposed assembly in Excel VBA

Answer 4

You might want to look at using the WCF Service Moniker which lets you invoke a WCF Service from VBA without installing anything on the Excel client machine other than the .NET Framework.

Dim addr As String
addr = "service:mexAddress=""net.tcp://localhost:7891/Test/WcfService1/Service1/Mex"","
addr = addr + "address=""net.tcp://localhost:7891/Test/WcfService1/Service1/"","
addr = addr + "contract=""IService1"", contractNamespace=""http://tempuri.org/"","
addr = addr + "binding=""NetTcpBinding_IService1"",bindingNamespace=""http://tempuri.org/"""

Dim service1 As Object
Set service1 = GetObject(addr)

MsgBox service1.GetData(12)

I've written out a complete step-by-step example.

/Damian