如何在自动化服务器列表中安装和注册用 VB.NET 编写的 COM Server for Excel? [英] How to get COM Server for Excel written in VB.NET installed and registered in Automation Servers list?

查看：27
本文介绍了如何在自动化服务器列表中安装和注册用 VB.NET 编写的 COM Server for Excel?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

Excel 2007、Windows Vista、VB.NET、带有 .NET 3.5 sp2 的 Visual Studio 2008、MSI 安装包.

Excel 2007, Windows Vista, VB.NET, Visual Studio 2008 with .NET 3.5 sp2, MSI setup package.

我有一个用 VB.NET 编写的 Excel UDF.它作为 COM 服务器公开，因为您不能直接在 .NET 语言中创建 Excel UDF.安装真的很痛苦，因为似乎没有一个安装设置完全正确；它们都没有为您提供将 COM 服务器放在客户端计算机上的安装包，其中服务器已注册，类型库已注册，并且组件在 Excel 2007 的自动化服务器列表中可见.

I have an Excel UDF that is written in VB.NET. It is exposed as a COM Server because you cannot create Excel UDFs in .NET languages directly. Installation is a real pain because none of the installation settings seem to get it quite right; none of them gives you an installation package that puts the COM Server on the client machine with the server registered, the type library registered, and the component visible in the Excel 2007's list of Automation Servers.

以下是类型库的安装设置，它们的缺陷在编译时和安装时很明显:

Here are the installation settings for type libraries, with their defects apparent at compile-time and install-time:

vsdrfComSelfReg

vsdrfComSelfReg

  • 在编译安装项目期间没有警告
  • 模块 xxx.tlb 注册失败.HRESULT -2147024703
  • 组件的 ProgID 和 GUID 在注册表中设置，但组件未出现在自动化服务器列表中

vsdrfDoNotregister

vsdrfDoNotregister

  • 编译时没有警告
  • 安装有效，但当然 TLB 未注册

vsdrfCOM

  • 编译时警告:警告:无法为名为xxx.tlb"的文件创建注册信息
  • 安装时未注册类型库

正确的设置应该是 vsdrfCOM，如这里:

The correct setting should be vsdrfCOM, as explained here:

问.谁能告诉我是做什么的vsdrfCOM 的意思是在一个安装项目中视觉工作室?当我可用时它可用检查属性注册"设置中添加文件的属性项目.

Q. Can anyone please tell what does vsdrfCOM mean in a setup project of Visual Studio? It is available when I check the property "Register" among properties of added files in a Setup project.

A.这意味着 Visual Studio 将在构建时提取 COM 注册数据时间并把它放在MSI文件中(主要是 MSI 文件的注册表，还有班级表).所以当你安装它你的代码不需要自我注册，因为文件得到复制到磁盘和注册表条目被创建.它也会通过创建类型库注册向 MSI 的 TypeLib 添加条目表.

A. It means that Visual Studio will extract COM registration data at build time and put it in the MSI file (mostly the MSI file's registry table, but also the class table). So when you install it your code doesn't need to self-register because the file gets copied to disk and the registry entries get created. It will also create type library registration by adding an entry to the MSI's TypeLib table.

许多困难似乎是 Vista 特有的.特别是，使用 REGCAP 实用程序从 .TLB 文件生成 .REG 文件在 Vista 中不起作用.如果不是这样，也许 这个建议会很有用.相反，它在运行时会生成空的 .REG 文件.

Many of the difficulties appear to be Vista-specific. In particular, using the REGCAP utility to produce a .REG file from a .TLB file does not work in Vista. If not for that, perhaps this advice would be useful. Instead, it produces empty .REG files when it works at all.

我已经尝试了 这个 StackOverflow 帖子.那个帖子很好地描述了技术问题:

I've tried all of the advice in this StackOverflow post. That post has a pretty good description of the technical problem:

参考"对话框中的条目框来自 HKCRTypeLib注册表项，而不是来自 HKCRCLSID.如果你的程序集没有出现在引用对话框但已编译 DLL仍然可以使用您的 COM 程序集，它意味着类和接口是为您正确注册程序集，但类型库本身不是.

The entries in the References dialog box come from the HKCRTypeLib registry key, not from HKCRCLSID. If your assembly does not show up in the References dialog but compiled DLL's can still use your COM assembly, it means the classes and interfaces were correctly registered for your assembly, but that the type library itself was not.

问题

有谁知道如何让安装注册组件和类型库?我无法访问 Windows XP 计算机.

The question

Anyone have an idea of how to make the installation register the component and the type library? I don't have access to a Windows XP machine.

详细说明为什么这很糟糕

.TLB 不是任何编译代码调用它所必需的.我没有像您一样尝试部署 Excel 自动化加载项，但我的猜测是 UDF 应该可以正常加载和运行.

The .TLB is not necessary for any compiled code to call it. I've not tried deploying an Excel Automation add-in, as you are doing, but my guess is that the UDFs should load and run just fine.

它与 Excel 中的不太一样.

It's not quite like that in Excel.

  • 用户打开工作表并尝试引用 UDF.未找到它，因为未加载 DLL.失败
  • 用户转到主页|Excel 选项|加载项|Excel 加载项+Go，并且 COM 服务器未列在加载项"对话框中.失败
  • 用户然后按自动化服务器以获取可用自动化服务器的列表.DLL 不存在.失败
  • 用户返回到加载项"对话框并选择浏览"，导航到安装目录，然后选择 DLL(XXX 不是有效的加载项")或类型库(您选择的文件不包含新的自动化服务器，或者您没有足够的权限......").失败

据我所知，用户必须从命令行运行 regasm.exe 才能使 Excel UDF/COM 服务器可用.如果告诉人们从命令行运行 regasm 以将加载项安装到 Excel，您感觉如何?

As far as I can tell, the user has to run regasm.exe from the command line to make the Excel UDF/COM server available. How would you feel about telling people to run regasm from the command line to install an add-in to Excel?

编辑 2009-10-04

迈克在下面的评论和指导很棒.我不知道的关键是安装程序有一个用于添加注册表项的内置注册表编辑器.哦，还有带有 ComRegisterFunctionAttribute 属性的安装函数没有被 Microsoft 安装程序调用.我已经从他引用的资料中获得了有关编写安装程序功能的说明.

Mike's comments and directions below are awesome. The key thing I did not know was that the setup program has a built-in registry editor for adding registry keys. Oh, and that the installation function with attribute ComRegisterFunctionAttribute was not being called by the Microsoft installer. I already had the directions about writing installer functions from the sources he cited.

推荐答案

我在周末尝试了部署自动化加载项.事实证明它非常复杂(这对您来说并不奇怪！)而且我在互联网上绝对找不到有关如何正确执行此操作的资源.没有任何.

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.

有描述如何使用 RegAsm 的来源，但没有描述如何正确使用安装项目来注册自动化加载项，这与您的标准 COM 加载项略有不同.

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:

如果您阅读了一些有关如何创建和注册 C# 自动化加载项的文章，您会发现您需要在 HKEY 添加一个名为 Programmable 的注册表项_CLASSES\_ROOTCLSID\{GUID}，其中 {GUID} 是您的 COM 可见类的 GUID.

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.

这通常通过添加一对由 ComRegisterFunctionAttribute 和 ComUnregisterFunctionAttribute.一个很好的例子来自文章 用 C# 编写自定义 Excel 工作表函数 作者:Gabhan Berry:

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;
}

转换为 VB.NET，这适用于:

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

ComRegisterFunctionAttribute 标记的方法在注册该类的程序集时由 RegAsm 自动调用.当该类的程序集通过 /u 开关注销时，ComUnregisterFunctionAttribute 标记的方法由 RegAsm 自动调用.

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.

问题是 ComRegisterFunctionAttribute 和 ComUnregisterFunctionAttribute 在通过 Visual Studio 安装项目安装时被完全忽略.

The problem is that the ComRegisterFunctionAttribute and ComUnregisterFunctionAttribute are completely ignored when installing via a Visual Studio Setup Project.

这起初似乎令人惊讶，因为 Visual Studio 安装项目使用 /regfile 开关运行 RegAsm 以生成包含所有必需注册表的 .REG 文件键.然后使用此 .REG 文件，然后在客户端站点运行 .MSI 包.

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.

来自 构建和部署 .NET COM 程序集 作者:Phil Wilson:

From Build and Deploy a .NET COM Assembly by Phil Wilson:

Visual Studio 如何解决COM 类注册条目?好，如果您已配置 Fusion Log查看器(.NET 2.0 中的 Fuslogvw.exeSDK) 来记录程序集加载、运行它在您的设置构建之后你会注意到 Regasm.exe 实际上在您的设置的构建期间运行项目.但是，它不执行任何注册.发生的事情是Visual Studio 运行 Regasm/regfile 用于创建 .reg 文件的选项包含注册表项需要获取信息第 1 步，这个 .reg 文件是内部导入到设置中项目.所以如果你想看什么班级报名入口 VisualStudio 将在 MSI 设置中创建，你可以自己运行 Regasm/regfile 选项

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

在我自己使用 /regfile 开关运行 RegAsm 时，我注意到 Programmable 开关没有被包括在内.然后我将日志记录放入由 ComRegisterFunctionAttribute 和 ComUnregisterFunctionAttribute 标记的方法中，发现它们在运行 RegAsm 时都被调用而没有 /regfile 开关，但在使用 /regfile 开关运行时不会被调用，也不会在通过 Visual Studio 安装程序创建的 .MSI 包运行时被调用项目.

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.

Regasm.exe 的帮助文件 确认这一点(强调):

The help files for Regasm.exe confirm this (emphasis added):

您可以使用 /regfile 选项来生成一个 .reg 文件，其中包含注册表项，而不是使直接修改注册表.你可以更新计算机上的注册表通过导入 .reg 文件注册表编辑器工具 (Regedit.exe).请注意，.reg 文件不包含任何可以由用户自定义寄存器构成功能.

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.

然后，解决方案是自己添加 Programmable 键.这可以按如下方式完成:

The solution, then, is to add the Programmable key ourselves. This can be done as follows:

  1. 在安装项目中，打开注册表编辑器.通过右键单击 HKEY_CLASSES_ROOT 文件夹，然后选择新建"和密钥"，在 HKEY_CLASSES_ROOT 下创建名为 CLSID 的新密钥.
  2. 在 CLSID 键下，添加一个以您的 GUID 命名的新键，包括花括号.
  3. 在您添加的新 GUID 键下，添加一个名为 Programmable 的键.您不需要在此键中放置任何值；但是，我们确实需要强制创建它.因此，右键单击Programmable 键并选择属性窗口".然后将 AlwaysCreate 属性更改为 True.
  1. 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'.
  2. Under the CLSID key, add a new key named for your GUID, including the curly braces.
  3. 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.

完成此操作后，您不再需要标有 ComRegisterFunctionAttribute 和 ComUnregisterFunctionAttribute 的方法，但是当您通过 RegAsm 而不是通过安装项目安装时，我仍然会将它们保留在那些场合.

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.

此时您已准备好部署.构建您的解决方案，然后右键单击您的安装项目并选择构建".然后，您可以使用创建的 Setup.exe 和 .MSI 文件部署到客户端计算机.

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.

然而，还有一点需要考虑的是，当通过 Excel 的加载项对话框添加自动化加载项时，将显示一条错误消息，指出找不到 Mscoree.dll，您要删除添加吗?-在?"或非常相似的东西.可以忽略此错误消息，无论您回答什么，您的加载项都会运行，但它可能会让安装加载项的客户端感到震惊.

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.

在 .NET 中为 Excel 编写用户定义的函数，作者 Eric Carter.

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.

问题是InprocServer32 键的默认值只是mscorree.dll，这足以让.NET 找到它，但会导致Excel 报错.解决方案是确保 InprocServer32 键的默认值包括系统目录的完整路径.例如，在 32 位 Windows 上，它应该读取 C:Windowssystem32mscoree.dll.但是，此路径需要有所不同，具体取决于安装它的系统.所以这条路径不应该被硬编码.

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 通过修改由 ComRegisterFunctionAttribute 和 ComUnregisterFunctionAttribute 标记的方法来处理这个:

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();
}

翻译成VB.NET，这相当于:

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

这有效，但有同样的问题，即在本地机器上运行 RegAsm 时正确注册了程序集，但在 Visual Studio 安装项目中尝试使用它时失败.

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.

同样，解决方案是添加我们自己的注册表项.然而，这一次，我们必须创建一个使用 [SystemFolder] 属性的默认值，它等效于使用的 System.Environment.SystemDirectory 调用在上面埃里克卡特的代码中.

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.

为此，在我们之前创建的 CLSID\{GUID} 键下添加一个名为 InprocServer32 的键.然后右键单击新的 InprocServer32 键并选择新建"，然后选择字符串值".结果将是一个名为 New Value #1 的新值，但您将处于编辑模式，允许您重命名它.您在这里要做的是删除所有字符，然后按回车.通过删除名称中的所有字符，您将创建一个默认值，并且注册表值的图标将自动重命名为(默认)".然后右键单击此默认值图标并选择属性窗口".在属性窗口中，将 Value 属性设置为 "[SystemFolder]mscoree.dll"(不带引号).

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.

还有最后一件事需要担心.如果您安装到 Excel 2007 或更高版本，上述内容将 100% 工作.但是，如果您在 Excel 2003 或更低版本上安装，则需要包含以下内容:

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:

修复:使用 Microsoft Visual Studio 2005 创建的加载项、智能文档或智能标记不要在 Office 中运行

Divo 给出了如何部署它的详细说明 此处.

A detailed explaination of how to deploy it is given by Divo here.

如果您不应用此修复程序，所有内容都会正确注册，您甚至可以成功添加自动化加载项——一切看起来都很好——但您的工作表功能将失败，您仍然会得到#NAME?结果错误.(但同样，对于 Excel 2007 及更高版本，您不需要此功能.)

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.)

所以，最终，TLB 无关紧要.在我所有的测试中，我使用了没有/TLB 开关的 RegAsm，并且在通过设置项目注册时没有包含任何 TLB.所以我从 Vista 中做到这一点没有问题，尝试将 TLB 文件添加到安装项目时出现问题.

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.

我希望这会有所帮助，Hugh，并希望其他任何可能在未来偶然发现此主题的人...

I hope this helps, Hugh, and hopefully anyone else who might stumble onto this thread in the future...

迈克

这篇关于如何在自动化服务器列表中安装和注册用 VB.NET 编写的 COM Server for Excel?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
C#/.NET最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆