Title: Add-in for MS Office Applications (Revised)
Question: This article shows you how to write an addin (plugin) for MS Office applications. First, I give you one base unit you may re-use to implement your features into the Office tools. It is not quite perfect yet, but it is a start. I will update it some day. Second, I give you a sample showing you how to work this class. This sample will help you to create add-ins for Outlook, Qord and Excel.
Office plug-ins are COM libraries that use the IDExtensibility2 Interface.
Answer:
IN THE BEGINNING
================
The idea behind this unit was, that I want to give my users a simple way of loading data from my (distr.) database application into their MS Word documents, without them having to cut & paste every single piece of data.
THE MS INFORMATION FLOW
=======================
Therefore, I started looking around for information on how to create add-ins for MS Office applications. The help, you'll get from MS is very little and all for their VB/C++ wizards. This is not going to help a Delphi programmer much.
At some point I found few information on which COM interfaces to use and how to implement them for my purposes. The solution I have created you will find below. Go ahead and try it, or read some more about it details now.
The approach shown below, will work on MS Office 2000 and probably MS Office XP.
IMPORTING TYPE LIBRARIES
========================
Some of the type libraries you need to create may conflict with your current (standard) Delphi settings. To aviod this, you should unload one of your component packages (menu: Component|Install Packages). Remove the check mark for the component "Borland Sample Automation Server Components". !!! Do not remove the Component Package!!!
After having unloaded these packages, you can go ahead and import the following type libraries:
\Program Files\Common Files\Designer\MSADDNDR.DLL
\Program Files\Microsoft Office\Office\MSWORD9.OLB
\Program Files\Microsoft Office\Office\EXCEL9.OLB
\Program Files\Microsoft Office\Office\MSOUTL9.OLB
\Program Files\Microsoft Office\Office\MSO9.DLL
To import these libraries, you have to go to "Project|Import Type Library...", select "Add..", an choose the files accordingly. Choose a path for the unit, and select "Create Unit". Repeat this procedure for each file.
CORRECTION THE IMPORTED TLB
===========================
After importing the MSADDNDR.DLL type library you will have to correct a few lines.
FOnConnection(Self, Params[0] {const IDispatch}, Params[1] {ext_ConnectMode}, Params[2] {const IDispatch}, Params[3] {var {??PSafeArray} OleVariant});
must be corrected to:
FOnConnection(Self, Params[0] {const IDispatch}, Params[1] {ext_ConnectMode}, Params[2] {const IDispatch}, Params[3] {var {??PSafeArray OleVariant});
(There is a closing comment } to many. A few lines (starting 445) that contain this error. You will find them, while compiling your project.
PLANING YOUR MS OFFICE ADD-IN
=============================
In order for your MS Office add-in to work you have to implement the IDTExtensibility2 interface, defined in the AddInDesignerObjects_TLB unit. This interface defines five routines, called by the Office applications.
OnAddInsUpdate: Is called by the office application when the list of installed add-ins has changed. You might want to take notice, otherwise you can leave the procedure just empty. It must, however, be implemented.
OnBeginShutdown: Is called, just before the add-in is unloaded. Your add-in should not accept any user input anymore.
OnConnection: This procedure is called when the add-in is loaded.
OnDisconnection: This procedure will be called, when the add-in is unloaded. Use this procedure to free up resources you have taken.
OnStartupComplete: This procedure is called when your add-in is loaded with the office application, automatically.
These five procedures are implemented by the unit shown below. You may override them as needed.
WRAPPER COMPONENT FOR THE OFFICE BUTTONS
========================================
Further, I have created a wrapper component for the office buttons you will create. This component will handle the buttons of your add-in. Further, it maps a few of the button properties, you can access directly. You might want to map more than the ones shown.
This wrapper component is rather simple. If you have any questions, add them to comments section. I will answer them.
THE BASE UNIT FOR YOUR OFFICE ADD-IN
====================================
Note: You will find a sample of how to use this base unit below.
unit uOfficePlugin;
interface
uses
OleServer, ActiveX, Classes, ComObj,
Office_TLB, AddinDesignerObjects_TLB, Excel_TLB, Outlook_TLB, Word_TLB;
type
TOfficeButtonClickEvent = procedure(
const Control: OleVariant; var CancelDefault: OleVariant
) of object;
TOfficeButton = class(TOleServer)
private
FCommandBarButton: _CommandBarButton;
FOnClick: TOfficeButtonClickEvent;
function GetCaption: WideString;
function GetShortCutText: WideString;
function GetStyle: MsoButtonStyle;
function GetTag: WideString;
function GetVisible: WordBool;
procedure SetCaption(const Value: WideString);
procedure SetShortCutText(const Value: WideString);
procedure SetStyle(const Value: MsoButtonStyle);
procedure SetTag(const Value: WideString);
procedure SetVisible(const Value: WordBool);
function GetTooltipText: WideString;
procedure SetTooltipText(const Value: WideString);
function GetState: MsoButtonState;
procedure SetState(const Value: MsoButtonState);
function GetHyperlinkType: MsoCommandBarButtonHyperlinkType;
procedure SetHyperlinkType(const Value: MsoCommandBarButtonHyperlinkType);
protected
procedure InvokeEvent(DispID: TDispID; var Params: TVariantArray); override;
public
procedure Connect; override;
procedure ConnectTo(aServerInterface: _CommandBarButton);
procedure Disconnect; override;
procedure Delete;
property Caption: WideString read GetCaption write SetCaption;
property Visible: WordBool read GetVisible write SetVisible;
property State: MsoButtonState read GetState write SetState;
property Style: MsoButtonStyle read GetStyle write SetStyle;
property HyperlinkType: MsoCommandBarButtonHyperlinkType
read GetHyperlinkType write SetHyperlinkType;
property Tag: WideString read GetTag write SetTag;
property ShortCutText: WideString
read GetShortCutText write SetShortCutText;
property TooltipText: WideString read GetTooltipText write SetTooltipText;
property OnClick: TOfficeButtonClickEvent read FOnClick write FOnClick;
end;
TOfficeButtonClass = class of TOfficeButton;
TOfficeAddIn = class(TAutoObject, IDTExtensibility2)
private
FOfficeButtonClass: TOfficeButtonClass;
FExcelApp: TExcelApplication;
FOutlookApp: TOutlookApplication;
FWordApp: TWordApplication;
protected
procedure OnConnection(
const Application: IDispatch; ConnectMode: ext_ConnectMode;
const AddInInst: IDispatch; var custom: PSafeArray
); virtual; safecall;
procedure OnDisconnection(
RemoveMode: ext_DisconnectMode; var custom: PSafeArray
); virtual; safecall;
procedure OnAddInsUpdate(var custom: PSafeArray); virtual; safecall;
procedure OnStartupComplete(var custom: PSafeArray); virtual; safecall;
procedure OnBeginShutdown(var custom: PSafeArray); virtual; safecall;
function GetCommandBar(
aCommandBars: Office_TLB._CommandBars; aName: WideString;
aCreateOnDemand: WordBool; aCreatePos: MsoBarPosition = msoBarFloating
): CommandBar;
function GetOfficeButton(
aCommandBar: CommandBar; aName: WideString; aCreateOnDemand: WordBool;
aOnClick: TOfficeButtonClickEvent;
aCreateCaption: WideString = '';
aCreateStyle: MsoButtonStyle = msoButtonCaption;
aCreateVisible: WordBool = True;
aCreateControlType: MsoControlType = msoControlButton
): TOfficeButton;
property OfficeButtonClass: TOfficeButtonClass
read FOfficeButtonClass write FOfficeButtonClass;
public
procedure Initialize; override;
property ExcelApp: TExcelApplication read FExcelApp;
property OutlookApp: TOutlookApplication read FOutlookApp;
property WordApp: TWordApplication read FWordApp;
end;
implementation
uses
SysUtils, Dialogs
{$IFDEF VER140}
, OleCtrls, Variants
{$ENDIF}
;
{ TOfficeButton }
procedure TOfficeButton.Connect;
var
PUnknown: IUnknown;
begin
// inherited Connect;
// connect the class to the office button
if FCommandBarButton = nil then
begin
PUnknown := GetServer;
ConnectEvents(PUnknown);
FCommandBarButton := PUnknown as _CommandBarButton;
end;
end;
procedure TOfficeButton.ConnectTo(aServerInterface: _CommandBarButton);
begin
// disconnect the class from the current office button
Disconnect;
// connect to the new office button
FCommandBarButton := aServerInterface;
ConnectEvents(FCommandBarButton);
end;
procedure TOfficeButton.Delete;
begin
// delete the button from the office bar
FCommandBarButton.Delete(EmptyParam);
end;
procedure TOfficeButton.Disconnect;
begin
// inherited Disconnect;
// disconnect the class from the current office button
if FCommandBarButton nil then
begin
DisconnectEvents(FCommandBarButton);
FCommandBarButton := nil;
end;
end;
function TOfficeButton.GetCaption: WideString;
begin
Result := FCommandBarButton.Caption;
end;
function TOfficeButton.GetHyperlinkType: MsoCommandBarButtonHyperlinkType;
begin
Result := FCommandBarButton.HyperlinkType;
end;
function TOfficeButton.GetShortCutText: WideString;
begin
Result := FCommandBarButton.ShortcutText;
end;
function TOfficeButton.GetState: MsoButtonState;
begin
Result := FCommandBarButton.State;
end;
function TOfficeButton.GetStyle: MsoButtonStyle;
begin
Result := FCommandBarButton.Style;
end;
function TOfficeButton.GetTag: WideString;
begin
Result := FCommandBarButton.Tag;
end;
function TOfficeButton.GetTooltipText: WideString;
begin
Result := FCommandBarButton.TooltipText;
end;
function TOfficeButton.GetVisible: WordBool;
begin
Result := FCommandBarButton.Visible;
end;
procedure TOfficeButton.InvokeEvent(DispID: TDispID;
var Params: TVariantArray);
begin
inherited InvokeEvent(DispID, Params);
// react to the standard office button events
case DispID of
-1: Exit;
1: if Assigned(FOnClick) then
FOnClick(Params[0], Params[1]);
end;
end;
procedure TOfficeButton.SetCaption(const Value: WideString);
begin
FCommandBarButton.Set_Caption(Value);
end;
procedure TOfficeButton.SetHyperlinkType(
const Value: MsoCommandBarButtonHyperlinkType);
begin
FCommandBarButton.Set_HyperlinkType(Value);
end;
procedure TOfficeButton.SetShortCutText(const Value: WideString);
begin
FCommandBarButton.Set_ShortcutText(Value);
end;
procedure TOfficeButton.SetState(const Value: MsoButtonState);
begin
FCommandBarButton.Set_State(Value);
end;
procedure TOfficeButton.SetStyle(const Value: MsoButtonStyle);
begin
FCommandBarButton.Set_Style(Value);
end;
procedure TOfficeButton.SetTag(const Value: WideString);
begin
FCommandBarButton.Set_Tag(Value);
end;
procedure TOfficeButton.SetTooltipText(const Value: WideString);
begin
FCommandBarButton.Set_TooltipText(Value);
end;
procedure TOfficeButton.SetVisible(const Value: WordBool);
begin
FCommandBarButton.Set_Visible(Value);
end;
{ TOfficeAddIn }
function TOfficeAddIn.GetCommandBar(
aCommandBars: _CommandBars; aName: WideString; aCreateOnDemand: WordBool;
aCreatePos: MsoBarPosition = msoBarFloating
): CommandBar;
begin
Result := nil;
if aCommandBars = nil then
Exit;
try Result := aCommandBars.Item[aName]; except end;
if (Result = nil) and aCreateOnDemand then
Result := aCommandBars.Add(aName, aCreatePos, EmptyParam, EmptyParam);
end;
function TOfficeAddIn.GetOfficeButton(
aCommandBar: CommandBar; aName: WideString; aCreateOnDemand: WordBool;
aOnClick: TOfficeButtonClickEvent; aCreateCaption: WideString;
aCreateStyle: MsoButtonStyle; aCreateVisible: WordBool;
aCreateControlType: MsoControlType
): TOfficeButton;
var
OfficeButtonIntf: CommandBarControl;
begin
Result := nil;
if aCommandBar = nil then
Exit;
OfficeButtonIntf := aCommandBar.FindControl(
EmptyParam, EmptyParam, aName, EmptyParam, EmptyParam
);
if OfficeButtonIntf = nil then
begin
if aCreateOnDemand then
begin
OfficeButtonIntf := aCommandBar.Controls.Add(
aCreateControlType, EmptyParam, EmptyParam, EmptyParam, EmptyParam
);
Result := FOfficeButtonClass.Create(nil);
Result.ConnectTo(OfficeButtonIntf as _CommandBarButton);
Result.Tag := aName;
Result.Caption := aCreateCaption;
Result.Style := aCreateStyle;
Result.Visible := aCreateVisible;
Result.OnClick := aOnClick;
end;
end else begin
Result := FOfficeButtonClass.Create(nil);
Result.ConnectTo(OfficeButtonIntf as _CommandBarButton);
Result.OnClick := aOnClick;
end;
end;
procedure TOfficeAddIn.Initialize;
begin
inherited Initialize;
FOfficeButtonClass := TOfficeButton;
FExcelApp := nil;
FOutlookApp := nil;
FWordApp := nil;
end;
procedure TOfficeAddIn.OnAddInsUpdate(var custom: PSafeArray);
begin
// nothing to be done in the base class
// will be called if the list of installed add-ins has changed
end;
procedure TOfficeAddIn.OnBeginShutdown(var custom: PSafeArray);
begin
// nothing to be done in the base class
// descending classes should free any memory
end;
procedure TOfficeAddIn.OnConnection(
const Application: IDispatch; ConnectMode: ext_ConnectMode;
const AddInInst: IDispatch; var custom: PSafeArray
);
var
App: OleVariant;
AppName: String;
begin
App := Application;
// find the type off application running that is loading the server
AppName := LowerCase(String(App.Name));
if Pos('outlook', AppName) 0 then
begin
// MS Outlook
FOutlookApp := TOutlookApplication.Create(nil);
FOutlookApp.ConnectTo(Application as Outlook_TLB._Application);
end else if Pos('word', AppName) 0 then begin
// MS Word
FWordApp := TWordApplication.Create(nil);
FWordApp.ConnectTo(Application as Word_TLB._Application);
end else if Pos('excel', AppName) 0 then begin
// MS Excel
FExcelApp := TExcelApplication.Create(nil);
FExcelApp.ConnectTo(Application as Excel_TLB._Application);
end;
end;
procedure TOfficeAddIn.OnDisconnection(RemoveMode: ext_DisconnectMode;
var custom: PSafeArray);
begin
if Assigned(FExcelApp) then
FreeAndNil(FExcelApp);
if Assigned(FOutlookApp) then
FreeAndNil(FOutlookApp);
if Assigned(FWordApp) then
FreeAndNil(FWordApp);
end;
procedure TOfficeAddIn.OnStartupComplete(var custom: PSafeArray);
begin
// nothing to be done in the base class
// descending classes should initialize itself here
end;
end.
A SAMPLE USING THIS BASE UNIT
=============================
This unit overrides both classes. The first class is TSimpleOfficeButton. This class implements one procedure only. As far as I know, you will have to do this for every button class you want to create.
Following you see the most important part:
cServerData: TServerData = (
ClassID: '{374BC1D3-4C87-4F46-82DC-623C1B74BCE5}';
IntfIID: '{000C030E-0000-0000-C000-000000000046}';
EventIID: '{000C0351-0000-0000-C000-000000000046}';
LicenseKey: nil;
Version: 100
);
Note: You will have to create a unique GUID for every Button Class implementation you create for the ClassID, only. You can do this by pressing Ctrl+Shift+G within your Delphi editor. Remove the square braquets!
The GUIDs for the IntfIID and EventIID are the MS GUIDs for the CommandBarButton and the CommandBarButtonEvents interfaces. Do not change those.
The second class overrides the Add-In class and implements the business logic of our add-in. In our case, it will load (create if running for the first time) a command bar and load a simple button within it.
When the user presses the button, a pop up message appears returning the name of the currently active document.
CREATING THE EXAMPLE
====================
Start Delphi and close all open documents. From the "File|New..." menu point go to the ActiveX tab and select ActiveX Library. Save the project under SimpleWordAddin. Next create an Active Server Object, from the ActiveX Library tab, too. A dialog will show. Set the CoClass name to TestIt, emove the check mark from the "Generate ..." check box and set the Active Server Type to "Object Context".
Save the unit as uTestIt.
Paste the code from below into the newly created unit.
unit uTestIt;
interface
uses
ComObj, ActiveX, AspTlb, SimpleWordAddin_TLB, StdVcl, uOfficePlugin,
oleServer,
AddInDesignerObjects_TLB, Office_TLB;
type
TSimpleOfficeButton = class(TOfficeButton)
private
protected
procedure InitServerData; override;
public
end;
TTestIt = class(TOfficeAddIn, IDTExtensibility2, ITestIt)
private
FCommandBar: CommandBar;
FSimpleButton: TOfficeButton;
procedure SimpleButtonClick(
const Control: OleVariant; var CancelDefault: OleVariant
);
protected
procedure OnStartupComplete(var custom: PSafeArray); override; safecall;
procedure OnBeginShutdown(var custom: PSafeArray); override; safecall;
public
procedure Initialize; override;
end;
implementation
uses
ComServ, Dialogs, SysUtils
{$IFDEF VER140}
, OleCtrls, Variants
{$ENDIF}
;
{ TSimpleOfficeButton }
procedure TSimpleOfficeButton.InitServerData;
const
cServerData: TServerData = (
ClassID: '{374BC1D3-4C87-4F46-82DC-623C1B74BCE5}';
IntfIID: '{000C030E-0000-0000-C000-000000000046}';
EventIID: '{000C0351-0000-0000-C000-000000000046}';
LicenseKey: nil;
Version: 100
);
begin
ServerData := @cServerData;
end;
{ TTestIt }
procedure TTestIt.Initialize;
begin
inherited Initialize;
OfficeButtonClass := TSimpleOfficeButton;
end;
procedure TTestIt.OnBeginShutdown(var custom: PSafeArray);
begin
inherited OnBeginShutdown(custom);
// free the taken resources
if FCommandBar nil then
begin
FCommandBar.Delete;
FCommandBar := nil;
end;
if FSimpleButton nil then
FreeAndNil(FSimpleButton);
end;
procedure TTestIt.OnStartupComplete(var custom: PSafeArray);
begin
inherited OnStartupComplete(custom);
// create the command bar for word
FCommandBar := GetCommandBar(WordApp.CommandBars, 'DelphiTestBar', True);
if FCommandBar nil then
begin
// create the command bar button
FSimpleButton := GetOfficeButton(
FCommandBar, 'SimpleButton', True, SimpleButtonClick, 'S&imple Button'
);
end;
end;
procedure TTestIt.SimpleButtonClick(const Control: OleVariant;
var CancelDefault: OleVariant);
begin
// response to the command bar button click event
ShowMessage('Current Document: ' + WordApp.ActiveDocument.Name);
end;
initialization
TAutoObjectFactory.Create(ComServer, TTestIt, Class_TestIt,
ciMultiInstance, tmApartment);
end.
REGISTERING YOUR ADD-IN WITH WORD
=================================
After compiling your sample, register your add-in with the "Run|Register ActiveX Server" from the Delphi menu.
Next you have to go into the Registry, using the RegEdit.exe application, installed with Windows.
Go to the key:
HKEY_CURRENT_USER\Software\Microsoft\Office\Word\Addins
Create a new sub-key (ServerName.InterfaceName):
SimpleWordAddin.TestIt
Within the new sub-key create a dword-value named:
LoadBehavior
and set its value to 3
The value for the load behavior is a combination defined as follows:
$00 - Do not load (disconnected)
$01 - Load (Connected)
$02 - Load with Office Application
$08 - Load on Demand
$16 - Load only next time the Office Application starts
Good luck,
Daniel