C# calling .dll and returning VBA.CollectionClass

[Ieuan]

Hello all and Happy New Year,

I've been having this problem for some time and thought I'd try to see
if anyone else out there has had the same problem or can give a little
help.

I'm using a website written in C# to call a .dll which in turn returns
a load of information. Most of this information is readable. However,
some of the information returned is of type VBA.CollectionClass.

I've found from Microsoft that .NET cannot understand these types of
collections and only VB6 can read them.

If the collection is a simple collection then I can get the information
out by casting. However, if the information isn't basic then the
information returned isn't readable.

Has anyone encountered this problem? I'm sure someone out there must
be calling dll's which return VBA.CollectionClasses...

Does anyone know whether the next version of .Net will be able to cope
with this?

Any help would be very much appreciated.

Ieuan Roberts

[Nicholas Paldino [.NET/C# MVP]]

Ieuan,

.NET can use the CollectionClass from VBA. You just have to set a
reference to MSVBVM60.dll (I believe that is it, it is in your system32
directory), and then look for it in the VBA namespace. You will find it
under the COM tab (you use it through COM interop) in the references dialog.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com
"Ieuan" <ie*************@hotmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

Hello all and Happy New Year,

I've been having this problem for some time and thought I'd try to see
if anyone else out there has had the same problem or can give a little
help.

I'm using a website written in C# to call a .dll which in turn returns
a load of information. Most of this information is readable. However,
some of the information returned is of type VBA.CollectionClass.

I've found from Microsoft that .NET cannot understand these types of
collections and only VB6 can read them.

If the collection is a simple collection then I can get the information
out by casting. However, if the information isn't basic then the
information returned isn't readable.

Has anyone encountered this problem? I'm sure someone out there must
be calling dll's which return VBA.CollectionClasses...

Does anyone know whether the next version of .Net will be able to cope
with this?

Any help would be very much appreciated.

Ieuan Roberts

[Ieuan]

Nicholas,

Thanks for the reply.

I've put in a reference to the dll you mentioned - this brings in a few
things into the references area including the VBA one you mention.

I then try to cast a VBA.CollectionClass passed back from my dll into a
VBA.Collection. This works correctly but only so far as it shows the
converted object I'm trying to look at as an object of type
VBA.CollectionClass and I can't get any further into the object. I'm
looking at this through the locals window.

If the object is a simple one dimensional array then I can manage a
foreach object in VBACollection and put the object into an arraylist.

However, I don't know what type most objects are - they could be an
array, an array of an array etc...
Any other suggestions would be most welcommed.

Ieuan

[Nicholas Paldino [.NET/C# MVP]]

Ieuan,

The reason for this is that you need to cast it to the interface that
exposes the functionality. VB6 mucked around with COM, exposing interfaces
and trying to pass them off as classes. It's the kind of thing that causes
this mess in the first place.

Look for an interface named _Collection in the VBA namespace, and cast
to an instance of that. You should be able to access all the functionality
that you need once your CollectionClass is cast to that.

CollectionClass is a managed wrapper created by when you add the
reference which you can use to create the class instance (since you can't
create an interface).
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Ieuan" <ie*************@hotmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Nicholas,

Thanks for the reply.

I've put in a reference to the dll you mentioned - this brings in a few
things into the references area including the VBA one you mention.

I then try to cast a VBA.CollectionClass passed back from my dll into a
VBA.Collection. This works correctly but only so far as it shows the
converted object I'm trying to look at as an object of type
VBA.CollectionClass and I can't get any further into the object. I'm
looking at this through the locals window.

If the object is a simple one dimensional array then I can manage a
foreach object in VBACollection and put the object into an arraylist.

However, I don't know what type most objects are - they could be an
array, an array of an array etc...
Any other suggestions would be most welcommed.

Ieuan

[Cor Ligthert]

Ieuan,

In addition to Nicholas, did you try to set a reference to
Microsoft.VisualBasic and than use the collection in that. This would in my
opinion be compatible with the VBA collection. The Microsoft.VisualBasic
namespace is a full part of the Framework.

I never tried it, just an idea.

Cor

[Ieuan]

Nicholas,

Thanks for your reply again.

I tried what you suggested and had no luck.

My code looks similar to this - where ciObj is the whole thing being
returned from the dll and Fields_Names being the VBA.Collection I'm
interested in:
VBA.Collection col1 = (VBA.Collection) ciObj.Fields_Names;
VBA._Collection col2 = (VBA._Collection) ciObj.Fields_Names;
VBA.CollectionClass col3 = (VBA.CollectionClass) ciObj.Fields_Names;

Under all 3 of these castings, looking through the locals window, the
values for col1, col2 and col3 are displayed with a value of
{VBA.CollectionClass} and you can't dig down into the actual dataa held
within that class.

Any further suggestions would be most welcommed.

Ieuan

P.S.
I mentionned in my earlier mail that providing the collection held a
basic array I could read it. I do this by:
ArrayList arTemp = new ArrayList();
foreach (object obj in VBA.Collection)
{
arTemp.Add(obj.ToString());
}

This works for array's as the object comes through as a string.

However, on a more complex collection, the object comes through a
{System.__ComObject} and I can do nothing with this.

[Ieuan]

Nicholas,

Thanks for your reply again.

I tried what you suggested and had no luck.

My code looks similar to this - where ciObj is the whole thing being
returned from the dll and Fields_Names being the VBA.Collection I'm
interested in:
VBA.Collection col1 = (VBA.Collection) ciObj.Fields_Names;
VBA._Collection col2 = (VBA._Collection) ciObj.Fields_Names;
VBA.CollectionClass col3 = (VBA.CollectionClass) ciObj.Fields_Names;

Under all 3 of these castings, looking through the locals window, the
values for col1, col2 and col3 are displayed with a value of
{VBA.CollectionClass} and you can't dig down into the actual dataa held
within that class.

Any further suggestions would be most welcommed.

Ieuan

P.S.
I mentionned in my earlier mail that providing the collection held a
basic array I could read it. I do this by:
ArrayList arTemp = new ArrayList();
foreach (object obj in VBA.Collection)
{
arTemp.Add(obj.ToString());
}

This works for array's as the object comes through as a string.

However, on a more complex collection, the object comes through a
{System.__ComObject} and I can do nothing with this.