I am really hoping someone here helps me out. Have been struggling
with this probelm since last 2 days now.
What I am trying to do is expose a very simple .NET (C#) class to
Excel as an addin via COM. And as I understand this is a very standard
process.
Below is the code that I am using and it all works fine on my LOCAL
computer. i.e after all the registration etc. I am able to open up my
excel and type in the C# function name i.e. =getname() and am able to
see the expected value. The real issue is when I try to copy this dll
and register it on someone else's computer it would not work.
I have ensured the registry looks the same both on my and others
computer after registering thru the regasm tool. Also on other's
computer I am able to go into excel Tools/Add-ins and see the .NET/COM
class that I just registered but its just when I try to fire one of
its methods in the spreadsheet I dont see anything but the standard
"#NAME?"
So this is the following I am doing:
1. Successfully compile the class lib C# project
2. Generate the key pair by running sn.exe -k key.snk and modify the
AssemblyInfo.cs appropriately and it all works fine
3. Once compiled I run the regasm command to generate the .tlb file
and register to the registry: C:\>regasm TestExcelAddin.dll /
tlb:TestExcelAddin.tlb and it all works fine
4. Add it to the GAC: gacutil /i TestExcelAddin.dll and that also
works fine (since its strongly named and all)
After that I go to Excel click on Tools/Add-ins/Automation and select
my registered addin class TestExcelAddin.Test and OK and call the
function in the spreadsheet and works fine.
Now following the same procedure on someone else's computer does not
work (they do have the same .net version and same excel version). On
others computers I am successfully able to run all the above commands
but its just when I type in the function in excel it shows me "#NAME?"
instead of the value.
So where am I going wrong. Please help. Below is the code for
reference:
namespace TestExcelAddin
{
[Guid("A641893E-BECE-4681-B136-D486F2B751E8")]
[InterfaceType(ComInterfaceType.InterfaceIsIDispatch)]
public interface _Test
{
[DispId(1)]
int GetDay();
}
[Guid("87C20D72-9582-4887-88EC-7CF9B84EB610")]
[ClassInterface(ClassInterfaceType.None)]
[ProgId("TestExcelAddin.Test")]
public class Test : _Test
{
public Test(){}
public int GetDay()
{ return (DateTime.Today.Day); }
[ComRegisterFunctionAttribute]
public static void RegisterFunction(Type type)
{
Registry.ClassesRoot.CreateSubKey(GetSubKeyName(type,
"Programmable"));
RegistryKey key = Registry.ClassesRoot.OpenSubKey(GetSubKeyName(type,
"InprocServer32"), true);
key.SetValue(
"", 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)
{
StringBuilder s = new System.Text.StringBuilder();
s.Append(
@"CLSID\{");
s.Append(type.GUID.ToString().ToUpper());
s.Append(
@"}\");
s.Append(subKeyName);
return s.ToString();
}
}
