Damian Mehers' Blog Evernote and Wearable devices. All opinions my own.

5Jul/0950

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:

image

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:

image

Switch the first endpoint to use TCP:

image

Also change the second to use mexTcpBinding:

image

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/

image

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…

image

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:

image

image

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:

image

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:

image

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:

image

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:

image

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:

image

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:

image

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:

image

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:

image

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:

image

Whereas Visual Studio actually tells you what the error is:

image

The maximum message size quote for incoming messages has been exceeded

image

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 (50) Trackbacks (3)
  1. 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.

  2. Hi Vinny,

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

    Thanks,
    Damian

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

    Thanks,
    King

  4. 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.

  5. 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.

  6. 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

  7. 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

  8. 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

  9. 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

  10. Just Beautyfull! Thank you very much for this article.

  11. 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.

  12. 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

  13. @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…

  14. 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

  15. 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

  16. 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

  17. 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

  18. 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

  19. 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

  20. 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!
    .

  21. 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.

  22. 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?

  23. 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.

  24. 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!

  25. Hi Kelvin – sorry, no idea if it works for Excel 2003 :-(

  26. Thanks a lot… I was looking for the same.. It saved me lot of time. Once again thanks a lot..

  27. 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.

  28. 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?

  29. Ignore my last post…just needed to reference my workstation via the IP address, or likely fully-qualified.name.

  30. 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.

  31. 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?

  32. 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.

  33. can any one please send me working copy of app.config and vba CODE for wcf net tcp binding

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

    Any help

  35. 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.

  36. Thanx King Wilder,My issue resolved after adding bindingNamespace to each endpoint.

  37. 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

  38. Images are broken. Please fix.

  39. 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 :)

  40. 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.

  41. Images are still broken :-(

  42. 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 ?

  43. 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..

  44. 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. :(

  45. 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?

  46. 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!

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

  48. Can you please take a look at passing an array. I’m trying to pass a variant array into a wcf function that takes an array of int. like here

    http://stackoverflow.com/questions/15200110/sending-array-data-from-excel-vba-to-wcf?rq=1
    and here
    http://stackoverflow.com/questions/2729245/passing-an-array-argument-from-excel-vba-to-a-wcf-service?rq=1

  49. Hi Damian, Great tutorial.
    I have implemented the same successfully, but can you please guide through increasing the MaxRecivedMessageSize property in an Excel.exe.config. I have tried the same on my side but it doesn’t work. I don’t want to implement add-in. Need Help so that i can increase the size 65536 to some other.

  50. I tried basically your sample works, even made variation net.tcp and basicHttpBinding. So big thanks for that.
    Now a real life service referencing KnownType… you’d run into ‘System.TypeLoadException’
    How do you handle that?


Leave a comment