How do I write a DLL for Excel in Delphi?

Question

I'm using Turbo Delphi 2006.

The DLL will be called from within Excel as part of a VBA/DLL combination.

The first part of the problem is trying to find out how to pass to the DLL a reference to the current Excel session. Most other code I've seen has you launching a separate instance of Excel apart from the one you're in.

I've seen some C++ code that creates an instance of IDispatch and then passes something in to a method of the IDispatch object, but not knowing much C++ ...

Any ideas?

Answer 1

I do not know much about Office, but I guess you should use COM/ActiveX. Then you also get your IDispatch. See http://delphi.about.com/od/comoleactivex/OLE_COM_DCOM_Automation_ActiveX_Delphi_knowledge_base.htm

Answer 2

Delphi comes with a set of ActiveX controls to give complete access to Excel and the other Office applications. They should be on the "Servers" tab of the Tool Palette.

If the aren't there, then select Components|Install Packages, and scroll down the list there until the very end, and select the right package.

In a default installation, they should be called:

Microsoft Office Sample Automation Server Wrapper Components

and there should be one for XP and Win2k. The XP ones will work for Vista.

Now that if if you want to automate Excel.

If you merely want to add functionality to Excel by using Delphi, I'd suggest using a COM object, as I suspect that Excel is very accepting of COM objects. Otherwise, you can create a straight DLL, and use that the same way that Excel uses any other DLL.

Answer 3

What you describe is called writing a COM addin. You need to create an automation DLL and implement the IDTExtensibility2 interface. You will then receive the Excel Application interface as a parameter to the OnConnection method.

You will also need to register your DLL as an addin so Excel will automatically load it.

EDIT: Forgot to mention: You might want to take a look at Add-in Express. Their framework and components make getting started with the creation of Office addins ridiculuously easy. You definitely won't have to bother with the details of IDTExtensibility2. All that comes with a (well-justified) price tag, though.