Sending array data from excel VBA to WCF

Question

My problem is, I want to send an array of strings from VBA to WCF.

c# code is :

[OperationContract]    
void SetSomeObjects(string[] data);

Here is the VBA Part

Dim data(2) As String
data(0) = "abc"
data(1) = "def"

Dim service2 As Object
Set service2 = GetObject(ServiceBindingInformation)
service2.SetSomeObjects data

The last line throws VBA

"Type Mismatch Error"

I have no idea why is this happening. Please suggest a way to send array data from VBA to WCF Service If I used object as the argument type, it gives error as shown in the attached screenshot

even with void SetSomeObjects(object data) , i get error "Type 'System.String[]' with data contract name 'ArrayOfstring:schemas.microsoft.com/2003/10/serialization/array is not expected. Add any types not known statically to the list of known types - for example, by using the KnownTypeAttribute attribute or by adding them to the list of known types passed to DataContractSerializer" — Hamda Binte Ajmal
– Hamda Binte Ajmal, Commented Mar 4, 2013 at 11:34
Have you tried to declare the string[] data with ref keyword: void SetSomeObjects(ref string[] data)? And Dim data(0 to 1) As string? Or dim data(0 to 1) as variant? — gembird
– gembird, Commented Mar 4, 2013 at 14:29

Kazimierz Jawor · Accepted Answer · 2013-03-07 09:43:47Z

0

I think the problem is within C# code.

Once I wanted to sort the array (VBA) based on Linq (C#). Array was passed to UDF in the way one pass an argument to the function/method:

Dim UDF_Array As UDFArrayLinqTest.ArrayLinq
Set UDF_Array = New UDFArrayLinqTest.ArrayLinq

TBL = Array(...)
Range(...) = UDF_Array.ArraySorted(TBL)

The simple UDF in C# was as follow:

public double[] ArraySorted(object tbl)
    {
        object[] obj = (object[])tbl;
        var filtr = from i in obj
                    orderby Convert.ToDouble(i)
                    select Convert.ToDouble(i);

        double[] result = (double[])filtr.ToArray();
        return result;
    }

I don't think it is the best idea but I stopped searching better one after I did that above which was good enough for me.

edited Mar 7, 2013 at 9:43

answered Mar 7, 2013 at 9:30

Kazimierz Jawor

19.1k7 gold badges38 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Hamda Binte Ajmal Over a year ago

thanks i will try it and let you know. Is this for WCF ? my binding is Net Named Pipe binding

Hamda Binte Ajmal Over a year ago

Hey, I tried it , it still does not work. How can the problem be in c# code when it never receives the array in the first case. Here is the code is used c# public void GetArray(object tbl) { object[] obj = (object[])tbl; }

Hamda Binte Ajmal Over a year ago

[VBA CODE] TBL = Array(1, 2, 3, 4) 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=""tempuri.org/""," addr = addr + "binding=""NetTcpBinding_IService1"", bindingNamespace=""tempuri.org""" Dim service1 As Object Set service1 = GetObject(addr) service1.GetArray (TBL)

Hamda Binte Ajmal Over a year ago

Gives the error, "Type System.object[] with data contract name 'arrayOfAnyType:Schemas...." is not expected. Add any types statistically to the list of known types, for example by using known type attribute or by adding them to list of known type by passing them to data contract serializer"

Kazimierz Jawor Over a year ago

My idea was prepared in C# VS Express, compiled and provide as COM solution. If you are going different way I'm not sure if I could help you.

Collectives™ on Stack Overflow

Sending array data from excel VBA to WCF

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related