Delphi XE4 MS-Office components

Delphi XE4 is delivered with 3 sets of Microsoft Office components (Word, Excel, Outlook, Power Point and Access): Office 2000, Office XP and Office 2010. None is installed by default.

To install Office components, you must launch the IDE, select "Component" menu and then "Install packages". In the list shown, you'll find "Microsoft Office 200 sample Automation Server Wrapper Components" and similar for XP. You don't see the package for office 2010 but it is delivered.

If you need Office XP or office 2000, just click the check box in front of the corresponding item in the list then click OK.

If you need Offcie 2010, click the "Add..." button below the list and navigate to "Program Files (x86)\Embarcadero\RAD Studio\11.0\bin" and select "dcloffice2010180.bpl". then click OK.

After installation of any one of the Office component package, you'll have a new tab "Servers" in the component palette with all the Office component wrappers.

By the way, always select the oldest version you can use because it will work with more recent Office version. Of course you can use recent Office functions only with recent component wrapper, but then your application will not work if an old Microsoft Office is installed.

Recommanded reading: "Automate Microsoft Office from Delphi" article available from my blog at http://francois-piette.blogspot.be/2013/01/automate-microsoft-office-from-delphi.html

Follow me on Twitter
Follow me on LinkedIn
Follow me on Google+
Visit my website: http://www.overbyte.be

Microsoft Word or Excel calls a Delphi application

 

This tutorial shows how you can have a Microsoft Office (Word, Excel,…) call your Delphi application. For the demonstration, I will use Word. From Word, a macro will call my Delphi application which will prompt the user for some data which will be inserted in the Word document.

 

In the real world, it is likely that your Delphi application will be a large application managing enterprise data. Calling it from Word or Excel will use existing function to fetch data and return it back to Word or Excel.

 

In this tutorial, we will: 

• Create a simple automatable Delphi application
• Create a Word VBA macro invoking the automatable Delphi application to get data and insert it in the document.

 

To build this tutorial I used Delphi XE3 and Word 2010. You can apply the same features using other Delphi or Word versions. Details may vary slightly with different versions but globally it remains the same.
 

 

Automatable Delphi application

 

Let’s create an automatable Delphi application!

 

Launch Delphi and create a new “VCL Forms Application” (File / New / VCL Forms Application – Delphi).

 

 

Save the application: Do Menu / File / Save project, name the main form unit “DelphiAppMain.pas” and the project file “DelphiApp.dproj”. Compile and run just to check everything is OK.

 

To make the application automatable, we need to add an “Automation object”. Do Menu / File / Other. Select Delphi projects / ActiveX on the left and select “Automation Object” on the right.

Click OK. On the next form, fill the fields as shown in this screen capture:
 

 

 

CoClass name “DataInterface” will be used in the VBA code we will see in a moment.

Description is anything you like to describe your application.

Threading model and Instancing will instruct the Windows COM engine about how to handle request. Using “single” and “Single instance” will make your application run automatically for each request. This may not be the best choice in all cases, but for now, it is the simplest and working choice suitable for this simple application.

 

Click OK to save your changes. This will create three files:

• DelphiApp.ridl (A type library source file)
• DelphiApp_TLB.pas (The type library imported into Delphi code)
• Unit1.pas (A class to implement the interfaces declared in the type library)

In the project manager (Ctrl+Alt+F11 if it is not displayed), you see the files in our project:

 

 

In the main window, where you normally see your code, you should now see the “type library editor”. If yoy don’t see it, do Menu / View / Type Library.

Right click on IDataInterface branch on the treeview. Click on “New” and select “Method”:
 

 

Change the name to “ReadData”:

 

“ReadData” is the name of the function we will call from VBA macro. Now we need to create and describe the arguments and return value. Since we intent to ask the user some data, we will pass two arguments and have a return value:

• A string to prompt the user
• A reference to a string to return the data
• Return value will be an integer

In the type library editor, there is a tab with parameters. There is button to add or delete parameters.

 

Data type deserve a little bit of explanations. Since OLE / COM / ActiveX is independent of the language, the data types are not only limited to a subset of what Delphi can handle, but their names is somewhat different than what Delphi uses. To make a long discussion short, here we need string and integer. Strings are named “BSTR” and integers are named “int”. We have in and/or out parameters. For “out” parameters, we must use a pointer. A pointer is specified by appending a start to the type name. So “int*” correspond to Delphi ^integer (A pointer to an integer).

 

The return value, as seen from VBA code, is an “out” parameter marked as “retval”. Do not confuse this return value with “return type” which should always be HRESULT is merely describe a low level API return value and type we don’t really care here.

 

With that knowledge, fill in the type library as the screen dump below shows:

 

 

Once the screen is as shown, click on the “Refresh implementation” tool button.

 

Click on the “Save All” button in Delphi main tool bar. This will prompt you for the implementation unit name currently named “unit1”. Name it “DelphiAppComInterface.pas”. You are also prompted for the type library file. Name it “DelphiApp_TLB.pas”.

 

Click on the DelphiAppComInterface tab to have a look at the source code which has been generated for you. You should see a single class named TDataInterface with a single method named ReadAdata, taking two WideString parameters Prompt and Value. The first is “const” , the second is “var”. This correspond to the “[in]” and “[in, out]” modifiers we used in the type library editor. The return value is of type SYSINT which is an alias of “integer”.

 

Here is the code:

 

Now we have to fill the gap and write the implementation code:

 

 

To use “InputQuery”, add “Dialogs” into the uses clause. ReadData, in my mind, is made to return a kind of error code. In a real application, you would query the data from some data source which might trigger several error conditions. ReadData should map those conditions to error codes and return it. Here in this tutorial, we just return 0 if OK and 1 as a single error code saying “not OK”.

 

We can compile and run the application which will just… do nothing!

 

Technically, the automatable application is an out of process COM object as Microsoft names it. Delphi runtime has everything required to build such a beast and this is exactly what we have done so far. Well, we just instructed Delphi to generate all the code except a single line…

 

As we wrote it, the application only responds when invoked from the outside via a COM interface. As it is now, that COM interface already exists but is almost unusable unless it is registered in Windows registry so that other applications can locate it, learn which interfaces are defined and call one of the interfaces methods.

 

Registering the application

 

COM object must be registered Windows registry. We don’t need to know all the complexity involved in that registration since Delphi runtime provides a method for doing exactly that.

 

When you build an application containing an automation object, Delphi runtime silently add command line argument processing to register and unregister your application in the OLE registry keys.

 

You need administrator privilege to be able to register your application. So first open a command line prompt with administrator privilege by right clicking on the command prompt shortcut and select “Run as administrator”. If asked, confirm. Then at the command prompt type de fill path name for your application between double quotes and add “/REGSERVER”. On my system, this gives:

 

      “D:\Delphi\BlogArticle\Office\Word To Delphi\Win32\Debug\DelphiApp.exe” /REGSERVER

 

Nothing happens on screen and you get back the command prompt almost immediately. Now DelphiApp is registered and can be used from any other application capable of accessing a COM object. And this is the case for Microsoft Office applications using VBA.

 

Writing a VBA macro to call DelphiApp

 

We need to write VBA code in Word. For that purpose, we have to make the “Developer” ribbon page available: right click on the ribbon where there is nothing and click “Customize the ribbon”. On the right list, search for “Developer” and check the checkbox, then click OK. You should now see the developer ribbon page displayed. (This may be different in older Word versions. Consult Word online help).

 

On the “Developer” tab is visible, click on “Visual Basic” button (Alt+F11). This will bring the Visual Basic IDE.

In Visual Basic for Application (VBA) you must add a reference to your automation object: click on the tools menu, then references and in the dialog box, search for your automation object and check the checkbox on the right of his name. In our case, the object is “DelphiApp”.

 

“DelphiApp” comes in that list because we registered our Delphi application. If you don’t see it, you probably forgot to register it. See the first part of this article to see which step you missed.

 

Next, you must write VBA code to call your Delphi application. Let’s enter this code in a new module associated with “Normal”. “Normal” is the template which is always used. This makes your macro available in all documents. In VBA project explorer (Ctrl+R), right click on “Normal”, and select “Insert” and then “Module”. You then see “Module1” added in the “Project Explorer” and a code window where you’ll enter your VBA instructions.

 

In the code window, you can add the following VBA code:

 

 

 

Once the macro is created, you may assign it to a keyboard shortcut or to a ribbon button. Let’s see how you can do that with Word 2010:

 

Right click on the ribbon where there is nothing and select “Customize ribbon” in the popup menu. You see 3 columns. Above the middle column, in the drop down list, select “Macros”. You should now see your macro “ReadDataFromDelphi”.

 

On the right side, you see all existing tabs. Click on the tab which you would like to be just on the left of the new tab. Below the list, click on the button “New tab”. This will create both a new tab and a new group. Click on the new tab. Click on the button “Rename”. Select a new name such as “DelphiApp”. Click on the new group. Click on the rename, enter a new name such as “Delphi” and select an icon.

 

Click on your macro in the middle column. Click on the button “Add” between the two right most columns. This will add your macro to the new group. Select a name such as “ReadData” and an icon.

 

Finally, click OK! You now should see the new tab “DelphiApp” with a single group “Delphi” having a single icon “ReadData”. Activate the tab and click the icon. Your Delphi program will start and display the InputQuery dialog box we programmed in Delphi.

 

My VBA code displays the value returned by DelphiApp using MsgBox. To insert the value into the document, replace the call to MsgBox by:

 

Selection.Text = Value

 

That’ it!

Suggested reading

Automate Microsoft Office from Delphi

The full article is available at
    http://francois-piette.blogspot.be/2013/01/microsoft-word-or-excel-calls-delphi.html

Follow me on Twitter

Automate Microsoft Office from Delphi

Microsoft Office (Word, Excel and others) are applications which can be fully automated from another application. Everything you can do by hand can also be done programmatically from another application and of course from your Delphi application.

Office API and Delphi components

Microsoft Office exposes his features thru a bunch of interfaces which are made accessible thru Windows COM API.

To ease the automation, Delphi is delivered with non-visual components which are "wrapper" around the underlying COM objects and exposes the same properties, methods and events as the COM interface.

Delphi has several sets of components for working with several Office versions. There are components for Office 2000, Office XP and Office 2010. 2010 version is available from Delphi XE2. Of course there are more Office versions, but don’t worry: Microsoft has carefully made his interfaces upward compatible. For example, if you use the Office 2000 components, you can still automate all Office versions from 2000 to the latest. The only restriction is that you cannot easily use new features if you use the old component.

Actually, it is better to use the older components which are capable of doing what you need to do!

This is because the compatibility in Microsoft office API goes upward and not downward.

Making the components available

Since there are several sets of components, you must make sure the correct version is installed.

For Office 2000 and Office XP, Embarcadero provides pre-built packages. For Office 2010, Embarcadero provides the source code but no package. Don’t worry, it is easy to create the package.

To see the installed packages, launch Delphi and go to the menu / Component / Install Packages. You see a long list of all installed design time packages with a checkbox telling that the package is available or not.

Locate “Microsoft Office 2000 Sample Automation Server Wrapper Components” (Or Office XP) and check the checkbox in front of the one you plan to use. Click OK and verify that your component palette now include a tab “Servers”.

To use Office 2010 components, first uncheck both Office 2000 and Office XP component. Then create a new package: go to menu / file / new / other. On the tree, select Delphi Projects and then on the right pane double click on the package icon.

Save the package in a convenient directory, naming it “Office 2010”. Then in the project manager, right click on Office 2010 and select “Add…”. The file open dialog is showing. Navigate to the directory where Delphi is installed, probably “C:\Program Files (x86)\Embarcadero\RAD Studio\10.0” and then navigate to “OCX\Servers\pas2010”. You’ll find all the files required to support the full Office 2010 suite. You may add all files but for the purpose of this article, only Word2010, Office2010 and VBIDE2010 are strictly required.

Once the files have been added, build and install the package: Right click on Office2010 in Project Explorer and select “Build” and then “Install”. In the process, you’ll be asked to add VCL framework. Accept. Finally, you have Office 2010 components installed and ready to be used. Strangely (Probably a small bug), then components are installed in the “OfficeXP” tab in the component palette and they all have the default icon. Not really a problem.

Using Office Components

As an example, we will create a sample application to insert a sentence at the end of a Word document. This is quick and easy!

Create a new VCL forms application, drop a TWordApplication and a TButton on the form. Then add the code below as the button’s OnClick handler.

The quickest way to locate it is to enter WordApplication in the component palette search tool.

TForm1.Button1Click(Sender: TObject);
begin
  WordApplication1.Connect;
  WordApplication1.Visible := TRUE;
  WordApplication1.Selection.EndOf(wdStory, wdMove);
  WordApplication1.Selection.Text := 'Delphi Rocks !' + #13;
  WordApplication1.Selection.EndOf(wdStory, wdMove);
  WordApplication1.Disconnect;
end;

Compile and run the application. Start Word, making both Word visible and your application. Click the button and see a line is added at the end of the Word document. Magic! You see that your Delphi application is automating Word.

To understand all the features you can do with Word automation, you must consult Microsoft documentation. Unfortunately, this documentation is written for Visual Basic. Don’t worry, whatever the language is, the object model, the functions and properties do not vary. This is only a matter of syntax. The above Delphi code is inspired from sample code provided by Microsoft.

This article is available at:
     http://francois-piette.blogspot.be/2013/01/automate-microsoft-office-from-delphi.html

Update: Delphi XE4 MS-Office components article

Follow me on Twitter
Follow me on LinkedIn
Follow me on Google+
Visit my website: http://www.overbyte.be