views:

127

answers:

2

I'm trying to connect excel to a WCF service, but I can't seem to get even a trivial case to work... I get an Invalid Syntax error when I try and create the proxy in excel. I've attached the visual studio debugger to excel, and get that the real error is "interface not found". I know the service works because the test client created by visual studio is ok... so the problem is in the VBA moniker string.

I'm hoping to find one of two things:

1) a correction to my moniker string that will make this work, or

2) an existing sample project to download that has the source for both the host and client that does work.

Here is the code for my VBA client:

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

MsgBox (addr)

Dim service1 As Object
Set service1 = GetObject(addr)

MsgBox service1.Test(12)

I have the following service:

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

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

It has the following config file:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>

  <system.web>
    <compilation debug="true" />
  </system.web>
  <!-- When deploying the service library project, the content of the config file must be added to the host's 
  app.config file. System.Configuration does not support config files for libraries. -->
  <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>

Edit:

The updated moniker that worked for me was as follows

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

I would create a client as a .NET object, register it as a COM object and access it from VBA via COM

UPDATE: I found this article:http://msdn.microsoft.com/en-us/library/ms752245.aspx

It seems that to use the moniker you have to generate a client, and then register it with COM, and then use its GUID as your type, not the type as you have it

Joon
while that is another way to make a connection, the point is trying to learn doing it the WCF way... but getting a baseline to work
tbischel
I'm using a metadata exchange contract. I've been working from the article you linked already... note the "Metadata Exchange Contract" section.
tbischel
A: 

I was able to fix this by adding some quotes to some key locations on the moniker string... unfortunately, attaching the debugger to Excel gives you less than useful feedback, so fixing it is an exercise in guess and check. In VBA, you need a double quote ("") around each string in the moniker. Additionally, the term "mex" in the mex address must be capitalized, even though I specified the address in the contract as lower case. Go figure.

tbischel
Do you mind updating/editing your original post to reflect the changes you made that worked for you? I am experiencing the same issue and have tried the things you changed.
Ben.Vineyard
updated the post with the changes I made.
tbischel