Accessing functions in a COM DLL in Excel

Dec 13, 2012 at 7:00 AM

Hello

I've been using Excel-DNA for about an hour. It's insanely good. 

Regarding COM DLLs, I have a PerlCtrl DLL which wraps Geometry::Formula and exposes most of the methods therein, viz

BOG_TypeLib Interface Definition 

General Information
Library:	BOG_TypeLib (PerlCtrl wrap of Geometry::Formula)
File:	P:\Bruce_Axtens\Internal\BOG\BOG.dll
GUID:	543AE434-0587-42CB-8DE6-87681BF53C11
Version:	1.0

Enumerations
This section lists enumerations exposed by BOG_TypeLib.


Interfaces
This section lists the Classes exposed by BOG_TypeLib.  For each class, the methods and events are listed.

BOGControl 

Methods

Function Circle(ByVal Formula As String, ByVal Radius As Double) As Double
The circle method provides an area, circumference, and diameter formula

I created a .NET DLL with TLBIMP, viz

Microsoft (R) .NET Framework Type Library to Assembly Converter 2.0.50727.42
Copyright (C) Microsoft Corporation. All rights reserved. Resolved referenced file 'p:\Bruce_Axtens\Internal\bog\BOG.dll' to file 'p:\Bruce_Axtens\Internal\bog\BOG.dll'. Type 'IBOG' imported. Type 'BOGControl' imported. Type library imported to BOG_TypeLib.dll

Then I wrote a simple BOG.dna file, viz

    <DnaLibrary> <ExternalLibrary Path="BOG_Typelib.dll" ComServer="true" />    </DnaLibrary>    

And then loaded the BOG.xll Now, can I get access to the functions of BOG.DLL by simply referring to them in with =function(args) or must I wrap each one? viz

Function Circle(sFunction As String, dRadius As Double) As Double
	Circle = CreateObject("BOG.Axtension.1").Circle( sFunction, dRadius)
End Function
Regards,
Bruce
Coordinator
Dec 13, 2012 at 7:47 AM
Edited Dec 13, 2012 at 7:48 AM

Hi Bruce,

External COM libraries won't be exported directly. Excel-DNA looks for static methods, while the COM methods will be instance methods.

You might actually find that your COM wrapper is already what Excel calls an 'Automation Add-In', and can be used without getting Excel-DNA in the way. The only trick is that Excel expects certain registry entries to be present before it will recognize your COM library as a valid 'Automation Add-In'. You can read a bit more, and see what those registry entries are, in a CodeProject article I wrote long ago (it's basically just a 'Programmable' subkey under the CLSID registration). One you add the registry entries, you're done. Automation Add-Ins have some issues, which is why I eventually went the .XLL route with Excel-DNA, but it might work well for you.

So if you do to go with Excel-DNA and grow it into a full Excel add-in, you'll have to make the wrappers. Depending on your .NET skills, you might write code that generates them and registers via ExcelIntegration.RegisterMethods.

Regards,

Govert