January 21, 2013

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

7 comments:

Arnaud said...

Creating the whole components wrappers is somewhat an expensive task.

What I prefer in most cases, is to use dynamic binding.

You can write code as such:

procedure TForm1.ButtonClick(Sender: TObject);
var
WordApp, NewDoc: Variant;
begin
{ Creates a Microsoft Word application. }
WordApp := CreateOleObject('Word.Application');
{ Creates a new Microsoft Word document. }
NewDoc := WordApp.Documents.Add;
{ Inserts the text 'Hello World!' in the document. }
WordApp.Selection.TypeText('Hello World!');
{ Saves the document on the disk. }
NewDoc.SaveAs('my_new_document.doc');
{ Closes Microsoft Word. }
WordApp.Quit;
{ Releases the interface by assigning the Unassigned constant to the Variant variables. }
NewDoc := Unassigned;
WordApp := Unassigned;
end;


Another trick: it can be difficult to find out all the right parameters and commands to be sent to the COM server.

What I usually do is to record a Visual Basic macro in Word, click on the actions (menus, buttons) I want to execute, then look at the generated code and translate it into Delphi.

FPiette said...

The components wrapper are available out of Delphi box. Not an expensive task to enable them.

Dynamic binding is much more complex to use because you don't have Delphi prompting you for a method or property! And error can't be catch before running the application. This has a very high cost in my opinion.

William Meyer said...

There seems to be a very real lack of information on the use of these components. The task with which I am now faced is to present the user with a list of available templates for labels, then to create a new document based on the selected template, and to populate that document with data extracted from within my application.

Ideally, I should manage all this without a sabbatical to familiarize myself with all the MSDN content.

Also, templates might be from MS, Avery (or other label makers), or local to the user's own facility.

Among other things, this may mean that I wish to present to the user a list of the fields in the document, and a means of mapping them to the content on a form in my application.

If you have any suggestions as to where I may locate documentation in support of such operations, I would be grateful to hear.

FPiette said...

William, the documentation is at Microsoft wbesite. Delphi components are just warppers around Microsoft API, so you have to look at Microsoft (MSDN) to find the information. Of course you'll se mostly VBA and C# examples, but this is the same information you need to apply with a Delphi syntax. Also the VBA online hep in Word is usefull. This is not installed by default with Office products. You have to check a checkbox for that during installation. You can rerun the Office installation to change your settings.
If you have questions, you could post it in Google+ group: https://plus.google.com/#communities/103113685381486591754 or LinkedIn group http://www.linkedin.com/groups?gid=101829.

Lotfi said...

Bonsoir François
Thanks for the tips
It works for me XE5, office 2013, for Delphi
but for C++ the compilation fails

pbs in VBIDE2010.hpp
[bcc32 Erreur] VBIDE2010.hpp(51): E2275 { attendu
Contexte d'analyseur complet
Unit1.cpp(6): #include Unit1.h
Unit1.h(11): #include c:\Users\Public\Documents\RAD Studio\12.0\hpp\Word2010.hpp
Word2010.hpp(18): #include c:\Users\Public\Documents\RAD Studio\12.0\hpp\Office2010.hpp
Office2010.hpp(17): #include c:\Users\Public\Documents\RAD Studio\12.0\hpp\VBIDE2010.hpp
VBIDE2010.hpp(29): namespace Vbide2010


here :

__interface _Windows;
typedef System::DelphiInterface<_Windows> _di__Windows;
typedef _di__Windows Windows;

Have you an Idea ?
Thanks

Winter said...

Hi, I had to add one line to the code to get it to work in 10.1 Berlin (trial).

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

Without the assignment of "doc" to WordApplication1.Documents.Add(EmptyParam, EmptyParam, EmptyParam, EmptyParam)it crashed and burned.

Thanks,
Winter

Anonymous said...

Won't compile. Doesn't like wdStory. Says [DCC Error] myapp.pas(lineno) E2033 Types of actual and formal var parameters must be identical.