Tuesday, 4 October 2011

VBA tooo WCF tooo C#

Recently I came across an issue where a VBA application model had to trigger a job in remotely running C# application. Suddenly polling via DB and creating a queue based mechanism was on cards, but this process involves so many elements and is resource intensive. So I thought there should be a better mechanism for the same.

And WCF was to answer my problem. I have seen some solutions online but was based on the WCF as a service but in my context I had to have a win form/console application and VBA had to trigger a job in those applications based remotely.

So I ported the service based solution to my needs and below the code illustrated and sample files for the C# and VBA solution.

(Note: Sometimes if the VBA call fails please instead of localhost use the ip-address of the PC on which the C# application is running.)

Sub testWcf()

Dim addr As String
Dim i As Long
addr = "service:mexAddress=""net.tcp://localhost:9001/hello/mex"","
addr = addr + "address=""net.tcp://localhost:9001/hello"","
addr = addr + "contract=""IHelloWorldService"", contractNamespace=""http://tempuri.org/"","
addr = addr + "binding=""NetTcpBinding_IHelloWorldService"", bindingNamespace=""http://tempuri.org/"""

Dim service1 As Object
Set service1 = GetObject(addr)

For i = 0 To 10

Debug.Print service1.SayHello("Test Message: " & i)

Next i

End Sub

And the associated C# model is as follows:

using System;
using System.Collections.Generic;
using System.Linq;
using System.ServiceModel.Channels;
using System.Text;
using System.ServiceModel;
using System.ServiceModel.Description;

namespace ConsoleApplication1

    public interface IHelloWorldService
        string SayHello(string name);

    public class HelloWorldService : IHelloWorldService
        public string SayHello(string name)
            Console.WriteLine("The param passed is :" + name);
            return string.Format("Hello {0}", name);

    class Program

        static void Main(string[] args)
            BindingElement bindingElement =
                new TcpTransportBindingElement();
            CustomBinding binding =
               new CustomBinding(bindingElement);
            Uri tcpBaseAddress =
               new Uri("net.tcp://localhost:9001/hello");
            ServiceHost host =
               new ServiceHost(typeof(HelloWorldService), tcpBaseAddress);
            ServiceMetadataBehavior metadataBehavior;
            metadataBehavior =
            if (metadataBehavior == null)
                metadataBehavior = new ServiceMetadataBehavior();
            host.AddServiceEndpoint(typeof (IHelloWorldService), new NetTcpBinding(), tcpBaseAddress);
               typeof(IMetadataExchange), binding, "MEX");
            Console.WriteLine("Server started @." + tcpBaseAddress);

Download Solution
Download solution 

Link: http://damianblog.com/2009/07/05/excel-wcf/

No comments: