Using VS 2008, here is my COM object
using System;
using System.Collections.Generic;
using System.Text;
using System.Runtime.InteropServices;
using System.Windows.Forms;
namespace TestCom
{
[Guid("9E5E5FB2-219D-4ee7-AB27-E4DBED8E123E")]
[ClassInterface(ClassInterfaceType.AutoDual)]
[ProgId("Test9.COMINT")]
public class TestComClass
{
public void Init(string userid, string password)
{
MessageBox.Show(string.Format("{0}/{1}", userid, password));
}
}
}
If I build this and register it on a production machine as follows
REGASM /CODEBASE TESTCOM.DLL
From a simple VB6 app this works fine
Private Sub Form_Load()
Dim o As Object
Set o = CreateObject("Test9.COMINT")
o.Init "A", "B"
End Sub
This exact same code called from VBA in Excel gives
"automation error" (0x80131700)
Everything works fine on a development machine, just not on a production machine with just .NET and MS Office installed.
I think this is something to do with the .NET framework not being initialized properly, when running under Excel. If I use Filemon I can see it skip around looking for MSCORWKS.DLL. When I call the same object from VBScript, it finds MSCorwks.dll fine.
When I called CorBindToCurrentRunTime
from VBA to try to forcibly load the CLR, interestingly I get the exact same HRESULT (0x80131700)
as when I do CreateObject()
in VBA.
Therefore I think it is a framework initialization issue.
I'm going to answer my own question, hopefully to spare others the hours of tedious drudgery I have just endured.
If you get this, it is because the .NET based COM assembly can't find the .NET framework
The solution is simple. Create a file containing the following
<?xml version="1.0"?>
<configuration>
<startup>
<supportedRuntime version="v2.0.50727"/>
</startup>
</configuration>
Call it "Excel.Exe.Config" and place it in the same directory as "EXCEL.EXE"
Problem solved!