Calling WCF Services from Excel VBA clients using the WCF Service Moniker

5Jul/0947

Calling WCF Services from Excel VBA clients using the WCF Service Moniker

Did you know there is a way to invoke simple Windows Communication Foundation (WCF) Services from Excel VBA without installing anything other than the .NET Framework on the Excel machine?

There are many different kinds of monikers, but the one that interests us is the Service moniker that can be used to access WCF Services:

Set someObject = GetObject(“service:Binding Information”)

In this blog post I’m going to show how you how to go about using WCF Services from Excel VBA, step by step, from beginning to end, and explain how to debug the rather obtuse messages that Excel displays when you’ve misconfigured something.

The WCF Service

The WCF Interface you expose can not be particularly complex if you want to use the Service Moniker – I’ve found it best to keep top primitive types, and arrays of primitive types. Data Contracts seem to be a no-go.

Start by creating a new WCF Service using File|New|Project and selecting WCF Service Library:

Double-click on IService1.cs in the Solution Explorer, and replace the default contents with this simple interface:

using System;
using System.ServiceModel;

namespace WcfService1
{
    [ServiceContract]
    public interface IService1
    {
        [OperationContract]
        string GetData(int value);

        [OperationContract]
        object[] GetSomeObjects();
    }
}

Next replace the contents of the service implementation, Service1.cs, with the following:

using System;

namespace WcfService1
{
    public class Service1 : IService1
    {
        public string GetData(int value)
        {
            return string.Format("You entered: {0}", value);
        }

        public object[] GetSomeObjects()
        {
            return new object[] { "String", 123, 44.55, DateTime.Now };
        }
    }
}

Configuring the WCF Service

By default the WCF Service is configured to use HTTP as the transport protocol. I generally switch it to use TCP, because I am operating within a corporate intranet, and HTTP seems like overkill.

You’ll also find that by default your WCF Service exposes two endpoints. The first exposes as you’d expect, the IService1 interface you defined above. The second exposes Metadata about your service, which the Service Moniker uses to know what operations are available on your service. You’ll need both.

Right-click on the App.config file in the Solution Explorer, and select Edit WCF Configuration:

Switch the first endpoint to use TCP:

Also change the second to use mexTcpBinding:

Change the base address that the service will use, to use a TCP address instead of a HTTP Address by selecting the Host node on the left hand tree, and then selecting the base address and clicking on Edit, and changing the text to be net.tcp://localhost:7891/Test/WcfService1/Service1/

Finally, because you are using TCP instead of HTTP, change the MetataData service to not expect to expose the metadata via HTTP, by changing HttpGetEnabled to False under Advanced|Service Behaviours…

Save the changes and exit the WCF Editor. Your App.config should look like this:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <system.web>
    <compilation debug="true" />
  </system.web>
  <system.serviceModel>
    <services>
      <service behaviorConfiguration="WcfService1.Service1Behavior"
        name="WcfService1.Service1">
        <endpoint address="" binding="netTcpBinding" bindingConfiguration=""
          contract="WcfService1.IService1">
          <identity>
            <dns value="localhost" />
          </identity>
        </endpoint>
        <endpoint address="mex" binding="mexTcpBinding" bindingConfiguration=""
          contract="IMetadataExchange" />
        <host>
          <baseAddresses>
            <add baseAddress="net.tcp://localhost:7891/Test/WcfService1/Service1/" />
          </baseAddresses>
        </host>
      </service>
    </services>
    <behaviors>
      <serviceBehaviors>
        <behavior name="WcfService1.Service1Behavior">
          <serviceMetadata httpGetEnabled="False"/>
          <serviceDebug includeExceptionDetailInFaults="False" />
        </behavior>
      </serviceBehaviors>
    </behaviors>
  </system.serviceModel>
</configuration>

To start running your service you can hit Ctrl-F5 in Visual Studio. This will start a test service host, and a test client. We will ignore the client, but you can check that the service host has started by clicking on the message in the notification area:

Eventually you’ll want to host your service somewhere else, such as a Windows Service.

The Excel VBA Client

Start Excel, and then hit Alt-F11 to enter the VBA Code editor. We’ll invoke the WCF Service whenever someone clicks on a cell in the first sheet. Double-click on Sheet1 on the top left hand side, and then select Worksheet from the drop down of objects:

The Service Moniker needs to know the address of the MetataData Service, the address of the service itself, binding information, and information about the contract.

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/"""

Once you’ve built up the Service Moniker string, you can use GetObject to resolve it, and then invoke a method on it:

Dim service1 As Object
Set service1 = GetObject(addr)

MsgBox service1.GetData(12)

The VBA Editor should look like this:

I’ve set a breakpoint on the first line of code by clicking where the red circle is above.

If you now click on a cell in your sheet, and let the VBA code run, you should see this:

The VBA code has called through to the WCF Service and invoked a method on it, and the service has returned a string.

Change the code to invoke the second method, and step through in the debugger, and add a Watch on the result:

Whats up with the Tempuri?

You’ll have noticed that the moniker string references a couple of weird Tempuri namespaces. These are the defaults, but you can alter them. This can be useful if you have a single service exposing a couple of interfaces and you only want to work with one of them. For example your service may expose a complex interface for WPF clients, and a simple interface for a Excel client. If you give the simple interface a different namespace than the complex interface, you’ll be able to access it from Excel VBA.

To change the contract namespace, edit your interface code:

using System;
using System.ServiceModel;

namespace WcfService1
{
    [ServiceContract(Namespace="http://damianblog.com/")]
    public interface IService1
    {
        [OperationContract]
        string GetData(int value);

        [OperationContract]
        object[] GetSomeObjects();
    }
}

Then your service moniker can be changed accordingly:

addr = addr + "contract=""IService1"", contractNamespace=""http://damianblog.com/"","

When it doesn’t work

The Excel message you get when the GetObject doesn’t work properly is not exactly detailed:

There is however a way of getting more detailed information. You can attach your debugger to Excel, and then see the underlying managed .NET exception.

To attach your debugger to Excel in Visual Studio use Debug|Attach to Process and then select Excel, and click Attach:

Next, make sure the debugger stops as soon as an exception occurs by clicking on Debug|Exceptions and check the checkbox to be interrupted when an exception is thrown:

Don’t forget that you have done this. If you leave it checked then later on when debugging unrelated code you may get thoroughly confused to have the debugger break inside framework code that throws and then handles an exception which is normally invisible to you.

You’ll also need to tell the debugger to break when code other than your own code throws an exception. Do this by clicking on Tools|Options and then going to the Debugging section, and uncheck the Enable Just My Code checkbox:

Now run your VBA code as usual, and when you get the error the Visual Studio Debugger will break with more detailed error information. So if I change my moniker string to use an invalid interface name the Excel VBA error is:

Whereas Visual Studio actually tells you what the error is:

The maximum message size quote for incoming messages has been exceeded

You’ll hit this error if your .NET code returns too much data to the Excel client. You might be able to resolve it by setting the MaxRecivedMessageSize property in an Excel.exe.config file in the same folder as Excel.exe. They way I have handled it is to move from using the Service Moniker mechanism, and to create a managed Excel AddIn that sets the maximum message size programmatically.

Summary

The WCF Service Moniker offers an excellent way for you to invoke simple WCF Interfaces, where small quantities of data are exchanged, without installing anything at all on the Excel Client machine.

In the above examples the service moniker created by the VBA code used the “localhost” address for the service, but if your service is running on another machine you can specify that machine’s address: the service can run anywhere.

Filed under: WCF Leave a comment

Comments (47) Trackbacks (2) ( subscribe to comments on this post )

Vinny
December 3rd, 2009 - 21:02

Hi Damian,
That’s a great and interesting article. Should credit you that you explained clearly. However I tried to implement exactly what you explained, and I could not have it working.

I get the Run-time -214722120 Automation error exception and I could not attach the excel application to the Excel.exe – VS did not handle the error.

Can you please help me and point where/what I am doing wrong?

Thanks,
Vinny.
dmehers
December 4th, 2009 - 21:58

Hi Vinny,

What happens when you try to attach the Visual Studio Debugger to the Excel process?

Thanks,
Damian
King Wilder
January 11th, 2010 - 22:31

Excellent article! I have just this need right now and didn’t know how to get started. Great work!

Thanks,
King
King Wilder
February 25th, 2010 - 02:19

Re: automation error after changing the ServiceContract Namespace -

After I got this error, I added the bindingNamespace attribute to the system.serviceModel web.config section for each endpoint and the error went away.
mike
March 17th, 2010 - 23:00

well done Damian, I’m needing this too, so I’m building and testing. Do you think it can do wsHTTPBinding besides the netTCP one you specify?
Thank you.
dmehers
March 17th, 2010 - 23:07

Hi Mike,

I don’t see why not off the top of my head … if you do try it, do let me know!

Regards,
Damian
Bill
May 2nd, 2010 - 23:37

Damian,
I can’t seem to figure something out. I have used your code to create a WCF service and used the service:binding method as detailed above. Everything works fine. But what I want to do is pass an int array to GetData. I change the calling type in Excel to

Dim values(0 to 2) of Integer

and I change the function interface to

public string GetData( int[] value )

but when Excel hits the call statement

return = service1.GetData( values )

I get the message Run-time error (80004003) Automation error Invalid Pointer

Have you tried passing an array to a WCF function?

Thanks,
Bill
damian
May 3rd, 2010 - 22:34

Hi Bill,

I think you can only pass variant arrays – try making it object[] value and pass in an array of variants, each of which is an int.

Regards,
Damian
Bill
May 4th, 2010 - 18:46

Damian,
Thanks for the suggestion. Tried changing the argument types to object[] and passed a variant array but I’m still getting:

Run-time error ‘-2147467261 (80004003)’
Automation error
Invalid Pointer

Any other thoughts? I’m going nuts trying to figure this out. Maybe I should just go back to a type library?

Bill
Lukas
May 10th, 2010 - 20:18

Just Beautyfull! Thank you very much for this article.
Devon Taig
May 17th, 2010 - 20:42

Thanks for the post. I’m actually working in C# as my client, BUT I can’t set a standard Service Reference at design time because the address and binding parameters will actually be entered by the human user of the application. So I’m hoping that I can use the technique you describe in this article to accomplish what I’m after. The stumbling block for me right now is that C# doesn’t have an equivalent for VBA GetObject does it? There’s Activator.CreateInstance, but you can’t pass in a single string. Any ideas are much appreciated.
Jagadish
May 30th, 2010 - 16:00

Thanks for this informative post. I had exactly the same requirement. However, i have one additional requirement – the WCF Service requires windows authentication and the client Excel VBA is in the intranet zone. what should i do so that the excel can authenticate itself to the WCF ? [Note: users are to be authenticated against the Active Directory]. Right now if i try to access the WCF from the Excel VBA, i get 401 error: unauthorized access, since the WCF is set to windows authentication.

Thanks in Advance for any input on this.

Regards
Jags
tbischel
June 4th, 2010 - 23:28

@Jagadish – You might want to look at this… I want to bump Bill’s post about passing arrays from VBA to the host. I haven’t been able to make this work yet, so if there are any new suggestions…
Chad Poplawski
July 9th, 2010 - 15:28

Damian,

First off, great post! I have an existing WCF service hosted as a Windows service that is currently being called by ASP.NET applications. Now we have a requirement that an existing Excel VBA application be able to act as a client of the WCF service. Using the code below I am receiving the following exception on the GetObject call:

InvalidCastException: “The object does not support the interface ‘cacc1e85-622b-11d2-aa78-00c04f9901d2′.”

Dim addr As String
addr = “service:mexAddress=”"http://serverName:portNumber/Enterprise/PGPEncryption/mex”",”
addr = addr + “address=”"net.tcp://serverName:portNumber/Enterprise/PGPEncryption/”",”
addr = addr + “contract=”"IPGPEncryption”", contractNamespace=”"http://tempuri.org/”",”
addr = addr + “binding=”"NetTcpBinding_IPGPEncryption”", bindingNamespace=”"http://tempuri.org/”"”

Dim serviceMoniker As Object
Set serviceMoniker = GetObject(addr)

Call serviceMoniker.EncryptFile(Application.Sheets(“Sheet1″).Cells(6, 2), Application.Sheets(“Sheet1″).Cells(7, 2), Application.Sheets(“Sheet1″).Cells(8, 2))

Set serviceMoniker = Nothing

Do you have any idea of what I may be doing wrong?

Best Regards,
Chad
Akhil
August 11th, 2010 - 12:55

Hi, I have my WCF contracts configured as OneWay only and the moniker(VBA client) is not working. I am using netTCPBinding. If I set IsOneWay = false , it works fine. Any hints? Thanks. Akhil
Duke
August 16th, 2010 - 20:18

Hi Damian,

Thank you for the excellent article and it made my venture into WCF a lot easier.

I have a question fro you though. I’m trying to call a WCF from an Excel form but need it to be a secure WCF service. I tried all over the internet to find an https implementation and consumption of a WCF service but didn’t have much luck.

Could you give us an example of how to call an https based WCF service in VBA?

Thanks
Duke
Pieter
October 15th, 2010 - 06:28

Hello Damian,

This article was a very good starting point for learning about the WCF service moniker. I have written an article that goes a little bit further with the subject. I demonstrate the use of typed contracts also and I show a way to debug the WCF service moniker without using the Visual Studio debugger.

http://pieterderycke.wordpress.com/2010/10/05/using-a-service-moniker-to-communicate-between-legacy-com-applications-and-new-net-applications/

Regards,
Pieter
JDA
October 18th, 2010 - 21:37

Hi Damian,

Good article but I’m stuck with an issue. My services are hosted under IIS (not sure it makes a difference with my issue).

I get the error message.
Interface not registered

Did you have to create a TLB and register it?

Regards
JD
Lavanya
November 18th, 2010 - 05:58

Hello Damian,
Thank you for such a detailed article. But I am having problems in getting this to work. I hit the notorious Run-time -214722120 Automation error exception and am unable to get VS to break on this exception. So no idea of what the actual underlying exception could be. Funny thing is that I am able to run a vbs script using the same code. But the same thing does not work from within Excel. Would be great if you can shed some light on this.

Thanks
Lavanya
VBAUser
January 13th, 2011 - 04:18

Hello,

Thanks for a great post. Got me quickly started. However, as you mentioned, I am facing the error – The maximum message size quote for incoming messages has been exceeded. Can you please elaborate on exel.exe.config. How should that look? Using VSTO is not an option for me. Searching on the web didnt provide me much info. Thanks much!
.
Jim Meehan
January 28th, 2011 - 18:49

I tried to implement your solution using a wcf service defined in .net 4.0 and an Excel 2003 app and it works for services that have parameters of a simple data type. However, when i try to implement it using a service with an input parameter of a complex data type, it fails saying that it cannot it cannot load type ‘*’ from assembly Systme.ServiceModel. Should it work for complex data types?

I also tried to configure the service moniker using a typed contract, but it would always fail getting the service moniker, saying that the interface is not registered.

I am very interested in using the service moniker, but I have found very little discussion or support for it on the web. Any help would be aprreciated.
Tahir
March 14th, 2011 - 18:07

Excellent article!

I have a quick question though. My client machine is a Windows 2000 server machine which of course cannot have .NET 3.5. Now can my VB6.0 client still communicate with my WCF service?
Issac
March 16th, 2011 - 05:59

Hello,

Thanks for your tutorial. However, I have same question of VBAUser, I am not able to know how to set the maxReceivedMessageSize in the excel.exe.config instead of setting the Excel Addin? I have read the MSDN http://msdn.microsoft.com/en-us/library/fde9akst.aspx and there is no additional information on its setting.

Thank you very much.
Kelvin
March 29th, 2011 - 01:47

Hi Damien,

Thanks for the great tutorial. I tried using the GetObject syntax on the VBA for Excel 2003 to call a WCF service that has a basicHttpBinding and MexAddress as endpoints, and it doesn’t seem to work. This seems ok with Excel 2007 and onwards. Would you know if GetObject for Excel 2003 support WCF that is exposed as basicHttpBinding or with a mexAddress? I tried this on Service Pack 3 of Office and Windows XP.

Thanks!
damian
March 29th, 2011 - 08:56

Hi Kelvin – sorry, no idea if it works for Excel 2003
Ershad
May 6th, 2011 - 13:18

Thanks a lot… I was looking for the same.. It saved me lot of time. Once again thanks a lot..
Sebastien
May 24th, 2011 - 14:05

Hi Damian, thank you for this tutorial (really useful), it works perfectly. Nevertheless I’m still have a limit with this solution, the netTcpBinding offer only 1minute of connection and unfortunately sometimes my Host program needs lot of more… Do you know how I can increase the binding TimeOut in vba ? I’m looking for a solution but I’m empty-handed.
james igoe
June 24th, 2011 - 14:05

I was able to get the service working as specified for myself in debug, but other users can not connect (actually send) to the service.

Anything obvious that I need to do?
james igoe
June 24th, 2011 - 14:47

Ignore my last post…just needed to reference my workstation via the IP address, or likely fully-qualified.name.
JA
July 4th, 2011 - 15:45

Great article, thanks Damian.

Any idea how can I set the timeout in Excel?

It keeps timing out after 1 minutes and don’t know how to change it. The fact that there’s no XML client .config doesn’t allow me to do it the regular way.

Also, it’d be good to know where can I find the sintax of what (and how) can go after “service:” in the configuration string (addr) you used to call GetObject.
JA
July 5th, 2011 - 09:28

More on my last post. I’m trying to increase the 1 minute sending timeout because I have service requests that last well over 1 minute. Some maybe even > 1 hour.

I’ve found al ink that explains what can go after the “service:” suffix.

http://msdn.microsoft.com/en-us/library/ms729739.aspx

I’ve tried adding a ‘bindingConfiguration’ and ‘mexBindingConfiguration’ parameters, but I always get a syntax error when instantiating the service via GetObject. I’ve tried several combinations of all the those parameters, but no joy.

Any ideas?
vv
August 3rd, 2011 - 20:27

I am getting following error while accessing wcf from Excel/vb.
Runtime error -2147221020(800401e4), Automation error

Please find app.config settings and connection string here.

<!–

–>

———–
Dim addr As String
addr = “service:mexAddress=”"net.tcp://localhost:8731/WcfServiceLibrary2/Service1/Mex”",”
addr = addr + “address=”"net.tcp://localhost:8731/WcfServiceLibrary2/Service1/”",”
addr = addr + “contract=”"IService1″”,”
addr = addr + “binding=”"netTcpBinding”",bindingNamespace=”"http://cat.com/Samples”"”

Dim service1 As Object
Set service1 = GetObject(addr)

MsgBox service1.GetData(123)
———————————————

can any one help me on where I am wrong in this.
vv
August 4th, 2011 - 14:09

can any one please send me working copy of app.config and vba CODE for wcf net tcp binding
Venkat
November 8th, 2011 - 21:16

Hi , I tried this getting Errror Message Target Principle name incorrect, This happends only when the Service is Hosted on Remote machines,

Any help
Shravan
November 22nd, 2011 - 12:08

Hi, I am getting Exception. I attached debugger to excel to see error. I got XmlException message- The ‘{‘ character, hexadecimal value 0x7B, cannot be included in a name.
Mukesh Rebari
January 7th, 2012 - 05:47

Thanx King Wilder,My issue resolved after adding bindingNamespace to each endpoint.
Msharan
February 16th, 2012 - 13:19

Hi, I am having maxReceivedMessageSize error on excel. My app.config is set up as mentioned. The problem is on excel. Could you please share how to dynamically set maxReceivedMessageSize in the excel addin. Thanks
Tom
March 15th, 2012 - 03:41

Images are broken. Please fix.
Thoma
May 23rd, 2012 - 10:08

Hi Damian,

First of all, thanks for your post, it was really useful to me.
I’m facing up to a new problem though. When i try to call my web service from my macro i get this error (in visual studio debugger) i get this error :

Could not load file or assembly ‘System.Xml.XmlSerializers, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′ or one of its dependencies. The system cannot find the file specified.

Would you have any idea where it comes from ?

PS : I’m using http protocol to do my request, I don’t know if this might help.

Thanks a lot
Ivo
May 25th, 2012 - 13:55

Hi Damian and all others,

I tried some example, which looks like this. I have service EvalService running on port 2012 (it runs as its returns configuration into webbrowser and svcutil is able to generate proxy/config), and I’m trying to call on it method NumberOfEvals which doesn’t take any parameter and returns long.
When I try to get service:
addr = “service:mexAddress=”"http://localhost:2012/EvalService/MexPoint”",”
addr = addr + “address=”"http://localhost:2012/EvalService/”",”
addr = addr + “contract=”"IEvalService”", contractNamespace=”"http://tempuri.org/”",”
addr = addr + “binding=”"WSHttpBinding_IEvalService”", bindingNamespace=”"http://tempuri.org/”"”
Set service = GetObject(addr)

I get it. But when I try to call NumberOfEvals from service,

Dim count As Integer
count = service.NumberOfEvals()

I get Automation Error “Run-time error ‘-2146233054 (80131522)’.
Seems like the method crashes, but when I’m trying to debug the server, the method is not in fact called. Does anybody have any idea, what can be a problem? Any hint is welcome.

The content of App.config file is:

Thank you.
Jack
July 16th, 2012 - 09:59

Images are still broken
Joseph Lee Hunsaker
August 18th, 2012 - 16:13

I am trying to construct the Moniker URL for a SharePoint 2010 List over http. I am using the /_vti_bin/ListData.svc/mex

What are the Moniker URL values for contract, contractNamespace, binding, and bindingNamespace ?
Navnit
August 23rd, 2012 - 09:18

Hi Damian and all others,

Any success in passing array / byte[] from vba to wcf webservice?
I haven’t been able to make this work yet, so if there are any new suggestions / solution..
Shrihari
November 2nd, 2012 - 14:51

Hi Damian,

It is a very nice article. I tried the same and is working fine.
Now I wanted to try consuming wcf service from c++. This is giving me error during invoke method. Can you please help me in this regards.

Steps we followed:
1 – Building a moniker
2 – CoGetObject – Success
3 – GetIdsOFNames – Success
4 – Invoke – Failed.
Keith
November 5th, 2012 - 16:15

Hmmmmm, trying to access an existing WCF service from Excel is very hard with these instructions. The following section seems like ‘magic’ and unfortunately is a roadblock:

“The Service Moniker needs to know the address of the MetataData Service, the address of the service itself, binding information, and information about the contract.

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/”"”

How do I start trying to figure this out for a WCF service that already exists?
Cal Braun
March 15th, 2013 - 17:01

Your comment is awaiting moderation.
Trying this out and getting a WSDL error. I posted this question to stackoverflow, any ideas?
http://stackoverflow.com/questions/15436383/getting-wsdl-error-when-calling-service-using-wcf-service-moniker
Thanks!
shabeer T
June 4th, 2013 - 10:03

Hi Damian, thank you for this tutorial (really useful), it works perfectly. Can we use the same way to pass userdefined data types???

Damian's Blog .NET from Geneva, Switzerland