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\_ROOTCLSID\{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
/regfile
option 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
/regfile
option
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 /regfile
option 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
CLSID
under HKEY_CLASSES_ROOT
by right-clicking on the HKEY_CLASSES_ROOT
folder, then choosing 'New', and then 'Key'.
- Under the
CLSID
key, 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 the Programmable
key and choose 'Properties Window'. Then change the AlwaysCreate
property to True
.
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:Windowssystem32mscoree.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 installi