Exposing a .NET interface to COM as an interface

---

When you define a NET interface along with a ComVisible class with your interface set as the default one , VB6 hides this default interface entirely from you. Then, when you want to work with that interface in VB6, you use the name of the implementing class. 

The Default interface is set either with ComDefaultInterface attribute or if this is left out, C# will make the default interface the first Interface that the Class implements.

So if you want to see the interface in VB 6.0 you should not have any class implement directly the default interface, but an interface that derives from the default one. Remember that for COM all the interface members of the base interface should be redefined in the derived interface using the new keyword.

How to quick wrap a .NET Collection in a COM component

Here is the code on how to quickly wrap up a .NET Collection and exposed it to COM so that it has a default Item property and a working For Each Loop.

These are the main changes I did to the code compared to my previous post ( see point 9 )

2) The Class NetProva inherits directly form SortedList, while before I used encapsulation and delegation to mimic inheritance. This means that we just need to code the GetEnumerator(). This is because we need this method to return the most generalized interface Collections.IEnumerator. Note the use of the new keyword in the code. We could not ovverride otherwise we would have got a System.Collections.IDictionaryEnumerator return type. Also note that we are return the Enumerator of the Keys becasue COM does not support the DictionaryEntry type.

3) The other method that we might need to implement.ovverride is the indexer. We did not need to do anything here.


TIP
You might be tempted to omit the inheritance relationship when defining a COM
interface because the base methods need to be defined anyway and you don’t have to deal with the
mess of multiply defined members. However, don’t omit the relationship because it’s
still important for proper operation on both .NET and COM sides. Not only does it
provide the expected behavior in .NET clients using such interfaces (such as implicitly
converting an INetProva type to an IEnumerable type, but for COM as well because
a CCW makes QueryInterface calls on the derived interface succeed for any of its
base interfaces.

namespace Collections01
{
    //  To expose properties and methods to COM, you must declare them on the class 
    //  interface and mark them with a DispId attribute, and implement them in the class. 
    //  The order in which the members are declared in the interface is the 
    //  order used for the COM vtable.
    //  ex:
    //  [DispId(1)]
    //  void Init(string userid , string password);
    //  [DispId(2)]
    //    bool ExecuteSelectCommand(string selCommand);

    //Class Interface
    [Guid("2c280db5-99d0-4b5d-a014-13f6a3dfe271"),
     ComVisible(true),
     InterfaceType(ComInterfaceType.InterfaceIsDual)]
    public interface INetProva : IEnumerable {
        [DispId(-4)] //Iterator
        new IEnumerator GetEnumerator();
        [DispId(2)]
        void Add(object key, object value);
        [DispId(3)]
        int Count { get; }
        [DispId(4)]
        void Remove(object key);
        [DispId(0)] //Default Property
        object this[object key] { get; set; }
   
    }



    // To expose events from your class, you must declare them on the events 
    // interface and mark them with a DispId attribute. 
    // The class should not implement this interface. 

    //Events Interface
    [Guid("23aaa0da-ba82-48a1-95fb-789e8e061be5"),
     ComVisible(true),
     InterfaceType(ComInterfaceType.InterfaceIsIDispatch)]
    public interface INetProvaEvents
    {
    }



    //The Class can also implement other interfaces. But only
    //the first one will be exposed to COM.
    //COM Class do not support inheritance beyond interface implementation
    //Class Employees : List<Employee> is not COM compatible

    //Class Implement the Class Interface
    [Guid("4a37e6a5-2efc-42d5-91db-52787a258d85"),
     ComVisible(true),
     ClassInterface(ClassInterfaceType.None),
     ComDefaultInterface(typeof(INetProva)),
     ComSourceInterfaces(typeof(INetProvaEvents)),
     ProgId("Collections01.NetProva")]
    public class NetProva : SortedList,  INetProva
    {
        public new IEnumerator GetEnumerator()
        {
            ICollection keys = this.Keys;
            return (IEnumerator)keys.GetEnumerator();
         
        }
    }
}

How to Deploy a .NET COM Assembly with VS 2008 Quick Guide

This is a very quick guide on how to deploy a COM exposed .NET dll using VS 2008.
For details please visit
 Build and Deploy a .NET COM assembly
Guidelines for COM interoperability from .NET: Heath Stewart Blog
Exposing .NET Framework Compoents to COM



1) Add to the Solution a Setup Up project:
   Right-click Colution -- Add -- New Project
   Other Project Types -- Setup and Deployment -- Setup Project
   Name it

2) In Fyle System on Target machine go to Application Folder
3) Add Assembly...
4) Navitate to the MyLibrary.dll COM expose assembly that you want to deploy
5) Click Ok. The .tlb file associate to the .dll will be imported too. The Detected Dependencied folder will show you also the Microsoft .Net Framework and the .tlb file

6) Click MyLibary.dll and in the Property Window select
    Register  = vsdraCOM
7) Click MyLibray.tlb and in the Property Window select
    Register = vsdrfCOM
 
Build the solution.

If you have a problem with the MS VS Regestry Capture utility see here

You will find in the Setup Folder a setup.exe and msi package ready to be deployed.

How to make VS 2008 to register a C# COM DLL

To make C# create and register the typelibrary go to
Project/Properties/Build tick Register for COM interop. (Reccomended Choice)

Otherwise you need to use REGASM (see here  for the asembly registration tool). The Assembly Registration tool reads the metadata within an assembly and adds the necessary entries to the registry, which allows COM clients to create .NET Framework classes transparently. This utility is necessary when you need to expose to COM e .exe (winform application) .net assembly. You can create a type libray in this way. MyAssembly.dll can be also MyAssembly.exe

It is important that you do not check the
Project/Properties/Application/Assembly Information... Make assembly COM-Visible.
This option will set [assembly: ConVisible(true)] in the AssemblyInfo.cs file
Which will make all the Class in the Project COM visible. This in practice will make C# to generate new Guids for each class on which we did not specify a Guid attribute each time we recompile, causing a registry bloat.
It is much better to set
[assembly: ConVisible(false)], and use ComVisible(true) at class level to specify which class should be visible for COM interop .

How to Register / UnRegister a C# COM Exposed DLL manually an with VS 2008

To Register a C# COM Exposed Dll you I reccomend to create a Register.Bat file with the following instruction

 ------------------------------------------------------------------------------------------------------
REM Set the search directories
path=%path%;C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727;C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319;C:\Program Files\Microsoft.NET\SDK\v1.1\Bin;C:\Program Files\Microsoft.NET\SDK\v1.1\Bin;C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322;

REM we first Unregister the .NET dll and then register it
regasm.exe /u "FullPath\MyLibrary.dll" /tlb
regasm.exe /codebase  "FullPath\MyLibrary.dll" /tlb
pause

-----------------------------------------------------------------------------------------------------

This insturction set the search path for the regasm.exe file. I have included the most common directory that should have it

path=%path%; .......

This line unregister the Dll and its typelibrary

regasm.exe /u "FullPath\MyLibrary.dll" /tlb


This one creates the type library and register the dll with its full Path and along the type library.
If you do not use the /codebase option VB will complain that it cannot find the library.

regasm.exe /codebase  "FullPath\MyLibrary.dll" /tlb

 To unregister make an unregister.bat file
 ---------------------------------------------------------------------------------------------
REM Set the search directories
path=%path%;C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727;C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319;C:\Program Files\Microsoft.NET\SDK\v1.1\Bin;C:\Program Files\Microsoft.NET\SDK\v1.1\Bin;C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322;

REM we Unregister the .NET dll
regasm.exe /u "FullPath\MyLibrary.dll" /tlb

pause

-----------------------------------------------------------------------------------------------------


When you use the  code base option you need to have a strongly type Assembly (Dll) To do this
follow these steps

Go to Properties/Signing. Tick Sign the assembly check box.
Then go to Choose a stroing name key file
Choose a name  ex   "MyLibrary_COM_Key"
This will create a MyLibray_COM_Key.snk file in the project folder.

Lastly you also need to make sure that the Assembly Version is not someting like 1.0.* which means that each time you rebuild the project the assembly name changes. Just give the assembly a version, something like 1.0.0.0 and stick with it. The call to the COM exposed DLL is also connected to the assembly version registered in the registry.

Microsoft Visual Studio Registy Capture Utility has stop working Error

If you have this error while you are trying to use VS 2008 Project Setup and Development, do the following:

1. Locate regcap.exe here: C:\Program Files(x86)\Microsoft Visual Studio 9.0\Common7\Tools\Deployment
2. Right click and select properties.
3. Select Compatibility tab
4. Check box to Run this program in compatibility mode.
5. Select Windows Vista SP2 in the OS drop-down and Run as Administrator.
6. Click Ok and Recompile.

his also works for 64 bit Windows 7 with VS 2010. The path for regcap is C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\Tools\Deployment 

MS scripting run time and MS Regular Expression in VB 6.0 or VBA

Hi,
Two nice library that help you speed up your VB 6.0 / VBA development time are the
1) Microsoft Scripting Run Time library

   The two most interestinjg objects here are the
   Dictionary object which is an hashtable. Its only major draw back  is that it does not expose the   NewEnum function, which means that while you can wrap it up to create a strongly type dictionary object, you cannot create a NewEnum method to loop through the collection using the For Each statement

   FileSystemObject which let you easily access to files and folders

2) Microsoft VBScript Regural Expression 5.5.

    This is a really nice library to use to test a string using regular expression.
    This is an example on how it works:

Function TestRegExp(myPattern As String, myString As String)
   'Create objects.
   Dim objRegExp As RegExp
   Dim objMatch As Match
   Dim colMatches   As MatchCollection
   Dim RetStr As String
   
   ' Create a regular expression object.
   Set objRegExp = New RegExp

   'Set the pattern by using the Pattern property.
   objRegExp.Pattern = myPattern

   ' Set Case Insensitivity.
   objRegExp.IgnoreCase = True

   'Set global applicability. It search the entire string
   objRegExp.Global = True

   'Test whether the String can be compared.
   If (objRegExp.Test(myString) = True) Then

   'Get the matches.
    Set colMatches = objRegExp.Execute(myString)   ' Execute search.

    For Each objMatch In colMatches   ' Iterate Matches collection.
      RetStr = RetStr & "Match found at position "
      RetStr = RetStr & objMatch.FirstIndex & ". Match Value is '"
      RetStr = RetStr & objMatch.Value & "'." & vbCrLf
    Next
   Else
    RetStr = "String Matching Failed"
   End If
   TestRegExp = RetStr
End Function

You can test your regual expression here
 An excellent introduction to regular expression can be found in this code project article

C# COM Exposed Collection

Code can be found here and here 
Visual Studio 2008 and Excel 2003-2008 required
Further details con be found on this post

Hi,
You can find attache a project that contains code that will allow you to create a C# COM exposed collection so that
1) It has Item as default property. So from VB 6.0 you can just do employees(1).Name
2) It is a IEnumerable object
3) It has a GetEnumerator() function that can be used to create VB 6.0 strongly typed collection that can be iterated using the For Each loop

 Public Function NewEnum() As IUnknown
   Set NewEnum = mNetList.GetEnumerator
End Function

Please do not to register the CLASS for COM interop just go to Project/Properties/Build Register for COM Interop.

Do not use the option Project/Properties/Application/Assembly Information/Make assembly COM Visible.
I am using the attribute  ComVisible(true) to decide which class should be visible to COM on individual basis.

How to access .Net library from VBA and VB 6.0

Download Example from Microsoft  here

Here you can find a couple of links that will explain you how to access the .Net Framework Class Libray (FCL) from VB6.0 and VBA.

This link contains a series of articles on COM and .NET
Link 1

This one shows you to use some .NET library withoug writing any line of code Link 2
In a netshell

1. Download and install either version 1.1 or version 2.0 of the .NET Framework. If you have installed Visual Studio .NET 2003 or Visual Studio 2005, or any of the Express products, then the .NET framework is already installed.
2. Execute Register.bat, which is included in the code download for this article. This registers the .NET framework System.dll so that it can be called as a COM object.
3. Start Visual Basic 6.
4. In the New Project dialog, select Standard EXE, and click OK.
5. Add a CommandButton and Image control to the form.
6. Set the Stretch property of the Image to true.
7. Select the Project | References menu command.
8. Click Browse.
9. For v1.1 of the .NET Framework, select C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\system.tlb. For v2.0 of the .NET framework, select C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\system.tlb.
10. Click OK. 

 Thies the the contens of the Register.bat file you can find on the link above.

path=%path%;C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727;C:\Program Files\Microsoft.NET\SDK\v1.1\Bin;C:\Program Files\Microsoft.NET\SDK\v1.1\Bin;C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322;
regasm "C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\system.dll"
regasm "C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\system.dll"
pause
  
as you can see it use the regasm utilty to register as a COM object the system.dll file.

More interestingly:

The FCL also ships with a number of powerful collection classes, which include:

• ArrayList—An array class that doesn't have a fixed size. You can just keep adding items to it.
• Hashtable—This class is similar to the Scripting.Dictionary class. You can add items and look them up by key.
• Queue—This is a first in, first out (FIFO) collection. You push items in, and then read them out at a later time in the same order.
• Stack—A first-in, last out (FILO) collection. You push items onto the stack, and then pop them off in reverse order.
• SortedList—Similar to the Hashtable, except when you iterate through the items, they're always sorted by the key.

 To use this classe just add

a reference to either C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\mscorlib.tlb or C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\mscorlib.tlb, depending on the version of the framework that you want to use. Mscorlib is part of the Microsoft .NET Framework, and contains the collection classes.

and you are ready to go.

The are few caveats though
1) Intellisense will not work!!!.  To sort this out you need to write your own COM wrapper (I will tell you how to do it)
2) To use the For Each loop to run through the collection you need to define a variable as IEnumerable and assign the list to this interface. This is because the For Each loop need an explicit GetEnumerable (equal to NewEnum) method to work
   
    Dim objSortedList As mscorlib.SortedList
    Dim Enum As mscorlib.IEnumerable
    Set Enum = objSortedList
3) If you follow step 2 instruction you will have the For Each loop work, however the single object returned for an HashTable or a SortedList is a DictionaryEntry. This means tha VB 6.0 will not be able to access anyway its properties
 
All of these problems can be solved coding your own COM class in C#.

This is explained on this Link3 you can also find some info on my blog here and I will soon post some sample projects

How to create custom collection in VBA tricks

This solution was taken from a forum entry I found on the web



You can, but the process is a bit more manual. If you export a .cls file from one of your VB6 proceedures and view it in a Notepad, you'll notice that some Attributes, not visible while editing your code, are added to the top of the routine(s).

The two properties in question will look something like this:
Property Get Item(Index As Variant) As Parameter
     Attribute Item.VB_UserMemId = 0
     Set Item = m_Collection.Item(Index)
End Property

Property Get NewEnum() As IUnknown
    Attribute NewEnum.VB_UserMemId = -4
    Attribute NewEnum.VB_MemberFlags = "40"
    Set NewEnum = Me.mCollection.[_NewEnum]
End Property

Now the above all looks "normal" except for the addition of the three "Attribute" Lines.

In the Item Property the line "Attribute Item.VB_UserMemId = 0" makes it the default property.

In the NewEnum, the "Attribute NewEnum.VB_UserMemId = -4" makes it the Default Enumeration Property (I'm sure you recognize the "-4" part.)

The Attribute NewEnum.VB_MemberFlags = "40" is to make the Enumerator a Hidden property, but, technically, this is not recognized in VBA, so it will be visible in IntelliSense, but I don't find that a big deal.

The solution is to (1) Make your Class, (2) SAVE, (3) Export the Class, (4) Remove the Class (steps 3 and 4 can be combined into one, as it asks you if you wish to "Export" when you right-click and choose "Remove") and then (5) Manually add the Attribute Lines as shown above, (6) Re-Import the edited Class.


(Btw, you can add the Attribute NewEnum.VB_MemberFlags = "40" line if you wish -- it won't hurt anything -- but it won't be recognized in VBA, it will just be quietly ignored. So there's no reason to bother doing this, really.)

As you know, editing the code thereafter has some propensity to lose these properties (even in VB6) and so this may have to be repeated occassionally. (A bit of a pain.)

The alternative is to create your class 100% within VB6 and then import it into your VBA Project.

Or, even better, make it in VB6, debugg it, get it running 100%, compile to DLL and then add this DLL to your references. This last concept is probably the most solid, but there could be deployment issues as your DLL now has to be correctly Registered on the Client machine. Not that this is a big problem, but it's not as easy as distributing a VBA Project...

Mail Merge with multiple To, CC, distribution lists and changing Subject

Link to Advance Mail Merge.doc
Link to Advanced Mail Merge DB.xls



In this two files you will find a way to extend the MS World mail merge to send email to
1)  Have multiple mails and distribution list in the To field
2)  Have multiple mails and distribution list in the CC field
3) Have a chaning subject

Also nothe the the merged field in the .doc document can be formatted
1) To format a date, toggle the field and add \@"DD MMMM, YYY"
2) To format a number add \##,##

In the attached document you will find an example.