How To Create A .NET Library Accessible From MS Access (Or Other Office Programs) Using COM Using C#

How to create a .NET library accessible from MS Access (or other Office programs) using COM using C#.

I recently had a client ask for the ability to access a .NET API from Microsoft Access. The library would need to be accessible from the references window in Visual basic for applications. VBA code could then be written to call the methods of the C# library and return any values to be used in VBA. This could be useful for example for calling a third party service which returns an access code.

To create a COM visible .NET library there are a few steps:

  • Create a COM visible C# interface for the method you want to call.
  • Make the main assembly COMVisible
  • Make the project COM visible – Register for COM Interop
  • Signing the assembly
  • Installing the assembly so it is visible from MS Access

A globally unique identifier (GUID) needs to be generated for the interface and the class that is being made COM visible. These can be generated using a site like https://guidgenerator.com/

Create a COM visible C# interface for the method you want to call.

VBA COM GUID interface

Make the main assembly COMVisible – implement the interface

Register for COM Interop

Signing the assembly

To sign the assembly a certificate needs to be generated.

Installing the assembly so it is visible from MS Access

The assembly can be installed into the global assembly cache (GAC) 

Another way to register the assembly at its location on the hard disk drive using the TLB register command.

 

.NET Framework/.NET

There are different versions of .NET that can be used to create *.dll files accessible from MS Office.

.NET Framework 4.8 is the last version before the newer .NET Core (now named simply .NET) became the main version.

Creating a library with .NET Framework for COM access is more straightforward as it generates the *.tlb file which is required to register the assembly and make it visible to VBA.

To create a library using .NET (formerly .NET Core)

With the latest versions of .NET a TLB file is not generated when a COM visible library is compiled or published.

A TLB library would need to be written.

There are further details here: https://learn.microsoft.com/en-us/previous-versions/windows/desktop/automat/creating-a-type-library

One  way involves using the MIDL compiler. This can be found by installing all of the C++ tools using the Visual studio installer.

This is advanced and beyond the scope of this article but can be done if you really need a type library (TLB file).

One thing to note is that I found it not possible to call static methods from COM. So for example if you are using a 3rd party NuGet package that has static constructors or methods you may not be able to call it from COM.  It is worth checking the source of the libraries you are calling first to check.  

Then you can save a lot of time by not building something that won’t work. Or save time by planning a workaround.  This applies to some cryptography libraries like generating JWT tokens using the Microsoft libraries. One way would be to write your own JWT token generation code.

Xonsole application

One way I found around having to generate a TLB file was creating a .NET console application with console app arguments.

The console app could then be called from VBA – passing in the parameters to the console app.

The console application output could then be read and returned as a value to the VBA code.

Another step was to set the console app compilation as self contained in the compiler settings. This meant that the outputted *.exe would have all of the *.dll file references required. This makes deployment to customer systems easier.