COM Interop with .NET 6.0 Excel（ VBA代码封装成Dll:dll .net 5.0）-iaspnetcore.com

Introduction
VB6 做出来的ActiveX DLL,都会有四个函数
- .Net 3.x
- .Net 2.x

Introduction

tools

OLE/COM Object Viewer

Windows10安装OLE/COM Object Viewer对象查看器

不要下载来路不明的软件。OLE/COM Object Viewer是Windows开发工具包内的一个工具，我们可以在微软开发者官网上下载，链接如下：

https://developer.microsoft.com/zh-cn/windows/downloads/windows-sdk/

how to write an idl file

https://www.codeproject.com/Articles/19605/Basics-of-an-IDL-file

I don't know if the tools still exist, but there used to be TLB to IDL converters. You could first make your assembly in .NET Framework 4.8 and generate the TLB. Then you feed the TLB into the converter to get back an IDL. Then you update the IDL with the GUIDs from your .NET 6 code. (You can skip this last step if you are using the same GUIDs.)

Anyway, handwriting an IDL is really easy if you know C or C++.

// Generated .IDL file (by the OLE/COM Object Viewer)
//
// typelib filename: COMThermo.tlb

https://csharpforums.net/threads/problem-net-6-com-and-excel-vba-revisitied.7724/

Client: Vb6
COM interop: C#.net
Launch Visual Studio
Create New Project
Select Visual C# - Class Library

.Net 6.x

a project that uses .Net 6 and is call from excel VBA

ComUnregisterFunctionAttribute

Indeed, much was lost with COM support during the .NET Framework to .NET Core3/5/6 translation for no apparent reason... From Excel you must add a reference to a "type library". That type library can be in a .tlb file or in a .dll if that .dll contains an embedded. .tlb. Have you built and embedded a.tlb in the .dll (it's possible starting with .NET 6 docs.

https://docs.microsoft.com/en-us/dotnet/core/native-interop/expose-components-to-com#embedding-type-libraries-in-the-com-host

.NET Core / .NET 6: Creating a TLB or DLL that can be added as reference in VBA

https://techcommunity.microsoft.com/t5/languages/problems-with-net-6-and-excel-vba/m-p/3062878

.Net 5.x

https://github.com/dotnet/samples/tree/main/core/interop/comwrappers

.Net 3.x

Unlike ASP.NET Core 2.0,

ASP.NET Core 3.0 and over removed the option to run on .NET Full Framework 4.x. It only runs on .NET Core, meaning you can no longer fall back to .NET Full Framework 4.x for COM Interop support.

dynamic excel = Activator.CreateInstance(Type.GetTypeFromProgID("Excel.Application", true));
excel.Visible = true;
Console.WriteLine("Press Enter to close Excel.");
Console.ReadLine();
excel.Quit();

As with .NET Core 2.0, the above code will function in .NET Framework 4.x.in .NET Core 5.x, an exception occurs:

System.__ComObject’ does not contain a definition for ‘Visible’

添加类库

删除 class1.cs

添加 com class 类

Step 1：编译时提示：未定义com 接口，不生成 com组件

Step 2：添加一个函数，编译时正常了生成com组件

step 3：VBA引用

After doing so I cannot find the library in the Access references window and if I browse to it and try to add it I get this error "Reference to selected file cannot be added"

错误提示：未能添加对“xxx.dll”的引用。请确保此文件可访问并且是一个有效的程序集或者COM组件

解析：添加引用–>浏览–>选择xxx.dll文件，点击“确定”，然后再在引用管理器中选择文件进行引用，但是怎么也没法引入该dll。

C#添加引用的类库需要这个Dll本身是.net的程序集，或者是一个COM组件才能正常引用，而今天我用的Dll是一个第三方的Dll文件，并非标准的.net的程序集，或者是一个COM组件，所以我们引入这种Dll类库时需要使用DllImport导入函数。

然后添加空间名引用：using System.Runtime.InteropServices;

在类中添加：[DLLImport(“xxx.DLL”)]，如果Dll不在.....\bin\Debug\下则需要制定路径

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
using System.Threading.Tasks;
 
namespace ConsoleApplication2
{
    class Program
    {
        [DllImport("fwlibe1.dll")]
        static void Main(string[] args)
        {
 
        }
    }
}

There are a number of structure that needs to be present to be recognized as COM library.

First, there needs to be at least DllRegisterServer() and DllUnregisterServer() function exports to be called by COM registration machanisms like "regsvr32.exe".

VB6 做出来的ActiveX DLL,都会有四个函数

DllRegisterServer、DllUnregisterServer、DllGetClassObject、DllCanUnloadNow

download dependency walker.zip :http://dependencywalker.com/

dumpbin

C:\Windows\System32>dumpbin /exports msxml6.dll
Microsoft (R) COFF/PE Dumper Version 14.14.26433.0
Copyright (C) Microsoft Corporation.  All rights reserved.


Dump of file msxml6.dll

File Type: DLL

  Section contains the following exports for MSXML6.dll

    00000000 characteristics
    5A499414 time date stamp Mon Jan  1 09:51:16 2018
        0.00 version
           1 ordinal base
           5 number of functions
           5 number of names

    ordinal hint RVA      name

          1    0 00002B49 DllCanUnloadNow
          2    1 0000ABC2 DllGetClassObject
          3    2 000255FD DllRegisterServer
          4    3 000C3CB1 DllSetProperty
          5    4 000255FD DllUnregisterServer

  Summary

        D000 .data
        E000 .reloc
       30000 .rsrc
      10B000 .text

C:\Windows\System32>

dumpbin

D:\tmp\ClassLibrary5\bin\Debug\net5.0>F:\Thirdprogram2\VC\Tools\MSVC\14.27.29110\bin\HostX64\x86\dumpbin.exe /exports  vbadll.dll
Microsoft (R) COFF/PE Dumper Version 14.27.29110.0
Copyright (C) Microsoft Corporation.  All rights reserved.


Dump of file vbadll.dll

File Type: DLL

  Section contains the following exports for VBADLL.dll

    00000000 characteristics
    60D875F8 time date stamp Sun Jun 27 20:58:32 2021
        0.00 version
           1 ordinal base
           4 number of functions
           4 number of names

    ordinal hint RVA      name

          1    0 00006F8E DllCanUnloadNow
          2    1 00006F62 DllGetClassObject
          3    2 00006F78 DllRegisterServer
          4    3 00006F4C DllUnregisterServer

  Summary

        2000 .data
        3000 .reloc
        2000 .rsrc
       27000 .text

dumpbin ClassLibrary5.dll

D:\tmp\ClassLibrary5\bin\Debug\net5.0>F:\Thirdprogram2\VC\Tools\MSVC\14.27.29110\bin\HostX64\x86\dumpbin.exe /exports ClassLibrary5.dll
Microsoft (R) COFF/PE Dumper Version 14.27.29110.0
Copyright (C) Microsoft Corporation.  All rights reserved.


Dump of file ClassLibrary5.dll

File Type: DLL

  Summary

        2000 .reloc
        2000 .rsrc
        2000 .text

.Net 3.x

In .NET 3.x projects with Visual Studio 2019, you can simply right-click on the project and select “Add COM Reference”.

.NET 5 and 6 are based on .NET Core. The following page provides information based on .NET Core 3

blog:https://docs.microsoft.com/en-us/dotnet/core/native-interop/expose-components-to-com

managed COM server in .NET Core 3.1 source code on github

.Net 2.x

.NET Core 2.0. That is, COM Interop works, but late binding using the C# dynamic keyword still doesn’t function.

dynamic excel = Activator.CreateInstance(Type.GetTypeFromProgID("Excel.Application", true));
excel.Visible = true;
Console.WriteLine("Press Enter to close Excel.");
Console.ReadLine();
excel.Quit();

https://bettersolutions.com/csharp/excel-interop/register-for-com-interop.htm

see:https://social.msdn.microsoft.com/Forums/en-US/62c819fe-338a-4792-b5d1-c8e9ebf55e0b/how-is-possible-to-add-a-dll-file-as-reference-in-a-vba-project

https://www.codeproject.com/Articles/7859/Building-COM-Objects-in-C

https://www.codeproject.com/Articles/18939/C-Com

2.VisioComAddinNet5 c#

https://github.com/nbelyh/VisioComAddinNet5

3.COM Interop with .NET Core 2.x .NET Core 3.x and .NET 5.0

Table of Contents

Introduction

Com Server

Excel VBA（Client）

sample code

tools

OLE/COM Object Viewer

how to write an idl file

.Net 6.x

.Net 5.x

.Net 3.x

VB6 做出来的ActiveX DLL,都会有四个函数

.Net 3.x

.Net 2.x