using a wcf service that contains a DataContract inside Excel VBA

Question

You read the title and groaned. That's okay. I did too. But we do what we're asked, right? I need to build a service that can be accessed via a moniker from within Excel (2003, but I'm assuming any version of Excel should support this functionality). At the moment all I want to do is have a spreadsheet post data to a WCF service running from a Windows service on a remote machine. Because that data needs to be retrieved by something a little more sophisticated than VBA, I decided to set up a data contract. Here's my code (at the moment this is just a proof-of-concept, but it's closely related to how it needs to look when it's finished).

Here's the WCF-related stuff:

Imports System.ServiceModel
Imports System.Runtime.Serialization

<ServiceContract()>
Public Interface IWCF

    <OperationContract()>
    Sub PutData(ByVal what As String)

    <OperationContract()>
    Function GetWhats() As TheWhats()

End Interface

<DataContract()>
Public Class TheWhats
    <DataMember()> Public Property Timestamp As DateTime
    <DataMember()> Public Property TheWhat As String
End Class

Public Class WCF
    Implements IWCF

    Shared Whats As New List(Of TheWhats)

    Public Sub PutData(ByVal what As String) Implements IWCF.PutData
        Whats.Add(New TheWhats With {.Timestamp = Now, .TheWhat = what})
    End Sub

    Public Function GetWhats() As TheWhats() Implements IWCF.GetWhats
        Return Whats.ToArray
    End Function
End Class

My app.config:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <system.web>
    <compilation debug="true"></compilation>
  </system.web>
  <system.serviceModel>
    <services>
      <service name="DataCollectionService.WCF">
        <endpoint address=""
                  binding="netTcpBinding"
                  contract="DataCollectionService.IWCF" />
        <endpoint address="mex"
                  binding="mexTcpBinding"
                  contract="IMetadataExchange" />
        <host>
          <baseAddresses>
            <add baseAddress="net.tcp://localhost:9100/DataCollectionService/ "/>
          </baseAddresses>
        </host>
      </service>
    </services>
    <behaviors>
      <serviceBehaviors>
        <behavior name="">
          <serviceMetadata httpGetEnabled="false"/>
          <serviceDebug includeExceptionDetailInFaults="true"/>
        </behavior>
      </serviceBehaviors>
    </behaviors>
  </system.serviceModel>
</configuration>

And my vba class to handle posting stuff:

Private Const pConxString As String = _
    "service:mexAddress=""net.tcp://localhost:7891/Test/WcfService1/Service1/mex"", " & _
    "address=""net.tcp://localhost:7891/Test/WcfService1/Service1/"", " & _
    "binding=""NetTcpBinding_IService1"", bindingNamespace = ""http://tempuri.org/"", " & _
    "contract=""IService1"", contractNamespace=""http://tempuri.org/"""

Public ServiceObject As Object

Private Sub Class_Initialize()
    Set ServiceObject = GetObject(pConxString)
End Sub

Public Sub PutData(ByVal what As String)
    ServiceObject.PutData what
End Sub

Private Sub Class_Terminate()
    Set ServiceObject = Nothing
End Sub

If I include the DataContract attribute and the function that returns the data contract object, my vba code fails in the Public Sub PutData method with the following:

"Instance of MessagePartDescription Name='GetWhatsResult' Namespace='http://tempuri.org/' cannot be used in this context: required 'Type' property was not set."

If I take out the DataContract and comment out the function in the service definition, I'm fine. I don't plan on using the GetWhats() function from within Excel. But yet I'm guessing it wants the type definition for TheWhats.

From what I've read one solution seems to be making this a COM object and referencing the DLL. However that isn't a workable solution for my environment. Is there another way to fix this?

Answer 1

Okay, answered my own question. The solution (at least in my case) is to split the interface and have my service class implement both interfaces. Here's my new interface file:

Imports System.ServiceModel
Imports System.Runtime.Serialization

<ServiceContract()>
Public Interface IWCF_WriteOnly

    <OperationContract()>
    Sub PutData(ByVal what As String)

End Interface

<ServiceContract()>
Public Interface IWCF_ReadOnly

    <OperationContract()>
    Function GetData() As TheWhats()

End Interface

<DataContract()>
Public Class TheWhats
    <DataMember()> Public Property Timestamp As DateTime
    <DataMember()> Public Property TheWhat As String
End Class

Public Class WCF
    Implements IWCF_WriteOnly
    Implements IWCF_ReadOnly

    Shared Whats As New List(Of TheWhats)

    Public Sub PutData(ByVal what As String) Implements IWCF_WriteOnly.PutData
        Whats.Add(New TheWhats With {.Timestamp = Now, .TheWhat = what})
    End Sub

    Public Function GetData() As TheWhats() Implements IWCF_ReadOnly.GetData
        Return Whats.ToArray
    End Function
End Class

That required a change in app.config so that two separate endpoints could operate on the same address:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <system.web>
    <compilation debug="true"></compilation>
  </system.web>
  <system.serviceModel>
    <services>
      <service behaviorConfiguration="GenericBehavior" name="DataCollectionService.WCF">
        <endpoint address="wo" binding="netTcpBinding" contract="DataCollectionService.IWCF_WriteOnly" />
        <endpoint address="ro" binding="netTcpBinding" contract="DataCollectionService.IWCF_ReadOnly" />
        <endpoint address="mex" binding="mexTcpBinding" contract="IMetadataExchange" />
        <host>
          <baseAddresses>
            <add baseAddress="net.tcp://localhost:9100/DataCollectionService/" />
          </baseAddresses>
        </host>
      </service>
    </services>
    <behaviors>
      <serviceBehaviors>
        <behavior name="GenericBehavior">
          <serviceMetadata httpGetEnabled="false"/>
          <serviceDebug includeExceptionDetailInFaults="true"/>
        </behavior>
      </serviceBehaviors>
    </behaviors>
  </system.serviceModel>
</configuration>

And because the write-only endpoint was isolated from the endpoint that required a data contract definition, this change in the Excel definition was necessary:

Private Const pConxString As String = _
    "service:mexAddress=""net.tcp://localhost:9100/DataCollectionService/Mex"", " & _
    "address=""net.tcp://localhost:9100/DataCollectionService/wo"", " & _
    "binding=""NetTcpBinding_IWCF_WriteOnly"", bindingNamespace = ""http://tempuri.org/"", " & _
    "contract=""IWCF_WriteOnly"", contractNamespace=""http://tempuri.org/"""

I tested this configuration with the WCF Test Client. I had to feed it the mex endpoint manually, but when I did, it picked up both contracts. I used the PutData method to populate the service class a little, then went into Excel and populated it some more. I went back to the WCF Test Client and ran the GetData function and it returned all the items added from both the Test Client and Excel.