I took a shot at deploying an automation add-in over the weekend. It turns out that it is enormously complicated (not a surprise to you!) and I could find absolutely no sources on the internet on how to do this properly. None.
There are sources that describe how to use RegAsm, but none how to correctly use a Setup Project to register an automation add-in, which is a little different from your standard COM add-in.
Fortunately, I was able to solve it. Here’s what I found out:
If you read some of the articles on how to create and register your C# automation add-in, you’ll see that you need to add a registry key named Programmable at HKEY\_CLASSES\_ROOT\CLSID\\{GUID}, where {GUID} is the GUID of your COM-visible class.
This is generally done by adding a pair of methods marked by the ComRegisterFunctionAttribute and the ComUnregisterFunctionAttribute. A good example of this comes from the article Writing Custom Excel Worksheet Functions in C# by Gabhan Berry:
// C#:
[ComRegisterFunctionAttribute]
public static void RegisterFunction(Type type) {
Registry.ClassesRoot.CreateSubKey(GetSubKeyName(type));
}
[ComUnregisterFunctionAttribute]
public static void UnregisterFunction(Type type) {
Registry.ClassesRoot.DeleteSubKey(GetSubKeyName(type), false);
}
private static string GetSubKeyName(Type type) {
string s = @"CLSID\{" + type.GUID.ToString().ToUpper() + @"}\Programmable";
return s;
}
Translated to VB.NET, this works out to:
'VB.NET:
<ComRegisterFunctionAttribute()> _
Public Shared Sub RegisterFunction(ByVal type As Type)
Registry.ClassesRoot.CreateSubKey(GetSubKeyName(type))
End Sub
<ComUnregisterFunctionAttribute()> _
Public Shared Sub UnregisterFunction(ByVal type As Type)
Registry.ClassesRoot.DeleteSubKey(GetSubKeyName(type), false)
End Sub
Private Shared Function GetSubKeyName(ByVal type As Type) As String
Dim s As String = ("CLSID\{" _
+ (type.GUID.ToString.ToUpper + "}\Programmable"))
Return s
End Function
The method marked by the ComRegisterFunctionAttribute is automatically called by RegAsm when the assembly for this class is registered. The method marked by the ComUnregisterFunctionAttribute is automatically called by RegAsm when the assembly for this class is being unregistered via the /u switch.
The problem is that the ComRegisterFunctionAttribute and ComUnregisterFunctionAttribute are completely ignored when installing via a Visual Studio Setup Project.
This seems surprising at first, because the Visual Studio Setup Project runs RegAsm using the /regfile switch in order to generate a .REG file containing all of the required registry keys. It is this .REG file that is then utilized then the .MSI package is run at the client site.
From Build and Deploy a .NET COM Assembly by Phil Wilson:
How does Visual Studio work out the
COM class registration entries? Well,
if you have configured the Fusion Log
Viewer (Fuslogvw.exe in the .NET 2.0
SDK) to record assembly loading, run
it after the build of your setup and
you’ll notice that Regasm.exe actually
runs during the build of your setup
project. However, it doesn’t perform
any registration. What happens is that
Visual Studio runs Regasm with the
/regfileoption to create a .reg file
containing the registry entries
required to get the information for
step 1, and this .reg file is
internally imported into the setup
project. So if you want to see what
class registration entries Visual
Studio will create in the MSI setup,
you can run Regasm yourself with the
/regfileoption
Upon running RegAsm myself using the /regfile switch, however, I noticed that the Programmable switch was not being included. I then put logging within my methods marked by the ComRegisterFunctionAttribute and ComUnregisterFunctionAttribute and found that they are both called when running RegAsm without the /regfile switch, but are not called when run with the /regfile switch, nor are they called when run via the .MSI package created by the Visual Studio Setup Project.
The help files for Regasm.exe confirm this (emphasis added):
You can use the
/regfileoption to
generate a .reg file that contains the
registry entries instead of making the
changes directly to the registry. You
can update the registry on a computer
by importing the .reg file with the
Registry Editor tool (Regedit.exe).
Note that the .reg file does not contain any registry updates that can
be made by user-defined register
functions.
The solution, then, is to add the Programmable key ourselves. This can be done as follows:
- Within the Setup Project, open up the Registry Editor. Create a new Key named
CLSIDunderHKEY_CLASSES_ROOTby right-clicking on theHKEY_CLASSES_ROOTfolder, then choosing ‘New’, and then ‘Key’. - Under the
CLSIDkey, add a new key named for your GUID, including the curly braces. - Under the new GUID key you added, add a key named
Programmable. You don’t need to put any value within this key; however, we do need to force it to be created. Therefore, right-click on theProgrammablekey and choose ‘Properties Window’. Then change theAlwaysCreateproperty toTrue.
Once you’ve done this, you no longer need the methods marked with ComRegisterFunctionAttribute and ComUnregisterFunctionAttribute, but I would still leave them in for those occasions when you intall via RegAsm and not via the Setup Project.
At this point you are ready to deploy. Build your solution and then right click on your Setup Project and choose ‘Build’. You can then use the created Setup.exe and .MSI files to deploy to a client machine.
Something else to consider, however, is that when adding the automation add-in via Excel’s add-ins dialog box, an error message will be shown stating that “Mscoree.dll cannot be found, would you like to delete the add-in?” or something very similar. This error message can be ignored, and your add-in will run no matter what you answer, but it can be alarming to a client installing your add-in.
This situation, and the explanation of how to solve it, is well described in the article Writing user defined functions for Excel in .NET by Eric Carter.
The problem is that the default value for the InprocServer32 key is simply mscorree.dll, which is sufficient for .NET to find it, but causes Excel to complain. The solution is to make sure that the default value for the InprocServer32 key includes the full path to your system directory. For example, on 32 bit windows, it should read C:\Windows\system32\mscoree.dll. This path needs to vary, however, depending on the system it is installed on. So this path should not be hard-coded.
Eric Carter handles this by modifying the methods marked by the ComRegisterFunctionAttribute and ComUnregisterFunctionAttribute to be the following:
// C#:
[ComRegisterFunctionAttribute]
public static void RegisterFunction(Type type)
{
Registry.ClassesRoot.CreateSubKey(
GetSubKeyName(type, "Programmable"));
RegistryKey key = Registry.ClassesRoot.OpenSubKey(
GetSubKeyName(type, "InprocServer32"), true);
key.SetValue("",
System.Environment.SystemDirectory + @"\mscoree.dll",
RegistryValueKind.String);
}
[ComUnregisterFunctionAttribute]
public static void UnregisterFunction(Type type)
{
Registry.ClassesRoot.DeleteSubKey(
GetSubKeyName(type, "Programmable"), false);
}
private static string GetSubKeyName(Type type,
string subKeyName)
{
System.Text.StringBuilder s =
new System.Text.StringBuilder();
s.Append(@"CLSID\{");
s.Append(type.GUID.ToString().ToUpper());
s.Append(@"}\");
s.Append(subKeyName);
return s.ToString();
}
Translated to VB.NET, this is equivalent to:
'VB.NET:
<ComRegisterFunctionAttribute()> _
Public Shared Sub RegisterFunction(ByVal type As Type)
Registry.ClassesRoot.CreateSubKey(GetSubKeyName(type, "Programmable"))
Dim key As RegistryKey = Registry.ClassesRoot.OpenSubKey(GetSubKeyName(type, "InprocServer32"), true)
key.SetValue("", (System.Environment.SystemDirectory + "\mscoree.dll"), RegistryValueKind.String)
End Sub
<ComUnregisterFunctionAttribute()> _
Public Shared Sub UnregisterFunction(ByVal type As Type)
Registry.ClassesRoot.DeleteSubKey(GetSubKeyName(type, "Programmable"), false)
End Sub
Private Shared Function GetSubKeyName(ByVal type As Type, ByVal subKeyName As String) As String
Dim s As System.Text.StringBuilder = New System.Text.StringBuilder
s.Append ("CLSID\{")
s.Append(type.GUID.ToString.ToUpper)
s.Append ("}\")
s.Append (subKeyName)
Return s.ToString
End Function
This works, but has the same exact problem where the assembly is properly registered when running RegAsm on the local machine, but fails when attempting to use this within a Visual Studio Setup Project.
The solution, again, is to add our own registry keys. This time, however, we’ll have to create a default value that makes use of the [SystemFolder] property, which is equivalent to the System.Environment.SystemDirectory call used within Eric Carter’s code, above.
To do this, add a Key named InprocServer32 under your CLSID\\{GUID} key that we created previously. Then right-click on the new InprocServer32 key and choose ‘New’ then ‘String Value’. The result will be a new Value named New Value #1, but you will be in edit mode allowing you to re-name it. What you want to do here is delete all the characters and then hit enter. By deleting all the characters from the name, you are creating a default value and the icon for the registry value will be automatically renamed “(Default)”. Then right-click on this Default Value icon and choose ‘Properties Window’. Within the properties window, set the Value property to "[SystemFolder]mscoree.dll" (without the quotes).
You can then right-click on your Setup Project and choose ‘Build’ and then you are ready to deploy.
There is just one last thing to worry about. If you are installing to Excel 2007 or above, the foregoing will work 100%. If you are installing on Excel 2003 or below, however, you will need to include the following:
A detailed explaination of how to deploy it is given by Divo here.
If you do not apply this fix, everything will register correctly, and you can even add your automation add-in successfully — everything seems fine — but your worksheet functions will fail and you’ll still get #NAME? errors as a result. (But, again, you don’t need this for Excel 2007 and above.)
So, in the end, the TLB does not matter. In all my testing I used RegAsm witout the /TLB switch and did not include any TLB when registering via the Setup Project. So I had no trouble doing this from Vista, which has issues when attempting to add a TLB file to a Setup Project.
I hope this helps, Hugh, and hopefully anyone else who might stumble onto this thread in the future…
Mike