Delphi 3 - Delphi and Microsoft Office: Automating Excel and Word

By: Charles Calvert

Abstract: The goal of this two-part paper is to get you up to speed automating Excel and Word from a Delphi application.

Delphi and Microsoft Office:
Automating Excel and Word

Copyright © 1997 by Charlie Calvert
Here is the examples.zip file.

The goal of this two-part paper is to get you up to speed automating Excel and Word from a Delphi application. Most of the text will focus on Excel, but you will find that if you understand Excel automation, that you need only a few hints to get started automating Word. If you are primarily interested in Word, I ask you to patiently read through the material on Excel, as almost all of it applies to Word.

You can use Delphi to fully control virtually all the features of Excel and Word. There is very little that you can do from inside Excel or Word that you cannot also automate from outside Excel or Word. In other words, both Excel and Word can be fully controlled from Delphi applications using OLE Automation.

For the most part the act of controlling Excel or Word from a Delphi application is not terribly challenging. Whatever difficulty there is comes not from Delphi's side of the equation, but from the innate complexity of the Excel and Word object hierarchies. Not that I find the hierarchies unreasonable, but they do encapsulate sufficient complexity to require a significant period of study. In particular, these automation classes give you rather detailed control over Word and Excel. Since these are complex applications, the interface to them also needs to be complex if it is not to be considered under powered. The purpose of this paper is to unscramble that hierarchy and show its underlying structure.

This paper is divided into two parts. The first part shows automating Word and Excel using variants and IDispatch, and the second part shows how to perform the same tasks with dispinterfaces and standard COM interfaces. All of these technologies are closely related. However, there are two significant differences between them:

  1. Using variants is usually the easiest, the most terse, but also the slowest way to get things done in terms of performance.
  2. Using COM interfaces is usually the most difficult, the most verbose, but also yields the highest performance.

There may be a short gap between the time the first and second parts of the paper are published.

When writing this paper I have attempted to explain things as clearly as possible. I'm aiming this paper primarily at intermediate or experienced programmers, but I hope it is accessible to anyone who has a basic understanding of how to use Delphi, Word and Excel. Though you should not need a high level of expertise to understand this paper, I am trying to cover the subject in some depth. Other sources, such as my book Dephi 2 Unleashed (Chapter 29), and the magazine the Delphi Informant (June 1997), covers some of this same material in a simpler, less in depth fashion. For many people a more high level, abstracted view may be more appropriate. But I believe there is also a big need for a more detailed look at this subject, which is why I have written this paper.

One final note: in my writing, I tend to use repetition to emphasize important points. This is a consciously exercised technique designed to make this paper as easy as possible to understand, while simultaneously ensuring that you don't accidentally skip over an essential point. Another benefit of repetition is that people tend to use articles of this type as a reference. As a result, they don't always read them from beginning to end every time they refer to them. As a result, it helps to give a one sentence review of key points that relate to the topic under discussion.

System Requirements
This paper was written against Delphi 3.01 and Microsoft Office 97. Portions of the paper would also work with Office 95, but the sections on interfaces, in particular, require that you use Office 97.

To perform automation successfully with Excel or Word you need a fairly powerful system with lots of RAM. I've been automating Excel for at least four years. When I first started out, I considered the technology a bit suspect simply because it was terribly slow. Now, however, our machines are powerful enough to take Excel through its paces in a few short moments. In particular, if you have a Pentium 120 class machine or above, and at least 48 MB of ram, then this technology works well for many types of projects. Excel or Word will now load quite quickly, and you can open and insert data into them in the blink of an eye. However, if you want to iterate over lots of data inside a Word or Excel document, then that can be a bit time consuming when compared to performing similar tasks inside of a Delphi application.

The bottom line here is that if you know Excel can do something well, and you know your target machines are powerful and will have Excel loaded on them, then there is no reason to search for third party components to perform spreadsheet related functions. Instead, you can just automate Excel from inside a Delphi application and get your work done professionally in just a few short hours. The icing on the cake is that you can then use MAPI to mail the results of your work to anyone who has a mail system and the ability to read Excel files. The point being that the recipient of your work need not actually have a copy of your Delphi application running when viewing the output from your program. Instead you can just send them the results in an Excel or Word document. Word document viewers can be downloaded for free from Microsoft's web site at www.microsoft.com.

Getting Started with Delphi and Excel
There are two different ways to run OLE automation from inside Delphi. One involves using interfaces, while the second involves using an OLE class called IDispatch along with a Delphi type called a variant. Interfaces give you the advantage of type checking your code on the client side, as well as relatively high performance. However, I am going to start the paper working with the somewhat easier to understand IDispatch and variants technology, and move on to cover interfaces after all the basics are clearly established. Do not worry if you don't yet understand the differences between the two techniques, as this subject will be cleared up over the course of the paper. At this stage, you just need to be aware that there are at least two ways to access OLE automation objects from Delphi, and that I am going to start out by showing you one that uses IDispatch and variants.

The following code, found in Listing 1, shows a bare bones example of a Delphi application that launches Excel. Just skim over the code for now, as I will spend the rest of this section of the paper explaining how it works.

Listing 1: The main form from the Excel1 application found with the code samples that accompany this article.

unit Main;

interface

uses
  Windows, Messages, SysUtils,
  Classes, Graphics, Controls,
  Forms, Dialogs, StdCtrls;

type
  TForm1 = class(TForm)
    Button1: TButton;
    procedure Button1Click(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
  private
  public
    V: Variant;
  end;

var
  Form1: TForm1;

implementation

uses
  ComObj;

{$R *.DFM}

procedure TForm1.Button1Click(Sender: TObject);
begin
  V := CreateOleObject('Excel.Application');
  V.Visible := True;
end;

procedure TForm1.FormDestroy(Sender: TObject);
begin
  if not VarIsEmpty(V) then
    V.Quit;
end;

end.

You can find this example on disk, in the program called Excel1.dpr. The code does nothing more than create an instance of Excel, make it visible, and then close it down when the user exists the Delphi application. The code does not check to make sure the user is not creating multiple instances of the application, but it does close down a single copy of Excel when you exit.

From Delphi's side, you should always start your automation applications by including COMObj in the uses clause. COMObj contains routines for retrieving OLE automation objects, and for dispatching calls to them. In particular, you usually use CreateOleObject to retrieve an automation object, and behind the scenes Delphi uses the COMObj routines VarDispInvoke, DispatchInvoke and GetIDsOfNames, to call an object. I will give you a bit more information on these routines in just one moment. The rest is simply a matter of using a built in Delphi type called a variant to reference the objects that reside inside Excel.

Here are a few simple lines of code that launch Excel from inside Delphi:

var
  V: Variant;
begin
  V := CreateOleObject('Excel.Application');
  V.Visible := True;
end;

The first line of code after the begin statement launches Excel. The call to CreateOleObject is relatively complex, so I will explain it in the next section of the paper.

After executing the first line, Excel will come up in the background, entirely offscreen, invisible to the user. This may, in fact, be the effect you want to achieve. However, when you first start out programming Excel, and whenever you are debugging your Excel automation application, you probably want to be able to see what is going on inside Excel. Therefore, I set the Visible property of the Excel Application object equal to True. This ensures that you can see what is actually happening on the Excel server. If you have thoroughly debugged your application you may want to skip this step, but I will include it in all the examples.zip I cover in this paper.

Of course, I haven't told you enough yet to make the code shown above entirely comprehensible. What is the purpose, for instance, of the variable V? What does CreateOleObject actually do?

As it turns out, the answers to these questions are non-trivial. The variable V is a variant, and CreateOleObject creates an instance of a COM object called IDispatch and returns it to you inside a variant. But saying as much doesn't help much if you don't understand COM, IDispatch, and variants.

There are three courses I could take at this point. One would involve an in depth explanation of COM and OLE, a second would give you only the minimal amount of information needed to keep going, and the third would be to find some middle ground. In this case I am going to opt for the latter solution, and leave it up to you to pursue COM in more depth on your own. I will however, discuss this subject over the next few paragraphs, and come back to it again in the second part of the paper. If you want even more information, some references to get you started are as follows:

  • I have an article on Delphi and DCOM on my WEB site: http://users.aol.com/charliecal, and also cover the subject in the book Delphi 2 Unleashed.
  • Microsoft has extensive documentation on this subject: http://ww.microsoft.com/com
  • The User's Guide from the Delphi 3 docs contains some text on this subject in Chapter 25.

The only thing you really need to know at this stage is that Microsoft has created a special type of object oriented programming called COM, which allows you to retrieve and call the methods of an object from a number of different languages. The COM object model is different from the one used by native Delphi programmers, so Borland gives you two choices:

You can followed Microsoft's lead and call the methods of these special objects off a variable type called a variant. This is the technique described in the first part of this paper.

You can follow a second, more technical approach and use interfaces or dispinterfaces.

One of the key differences between using interfaces and using variants is that interfaces allow you to call COM objects using the much faster dispatching technologies native to Object Pascal. As I will explain in Part II of this article, dispinterfaces follow a middle path between the variant technology and the interface technology.

COM is the underlying object model that makes OLE and ActiveX programming possible. At times I will use OLE and COM as virtual synonyms. ActiveX is yet a third very closely related technology, but I will not touch on it in this paper. However, it no longer incorrect to also use the words COM and ActiveX as virtual synonyms.

Variants get their name because they can provide a wide variety of functions, depending on the circumstances. For instance, they can contain a string, an integer, or, in special cases, a COM object. In other words, the type of variable held in a variant varies from one occasion to the next. That's why they call them variants. (For more information, look up "Variant Types" in the Delphi online help, or else look at the declarations for the structures used with variants at the top of System.pas.)

CreateOleObject calls a number of internal system wide OLE functions. The end result of these series of calls is that the function returns a COM object to you containing an interface to the object you want to call. In particular, you get back a variant that is wrapped around a COM object called IDispatch. A combination of the built in IDispatch methods, and various Delphi technologies covered briefly later in this paper, allows you to call the methods of the object your requested.

With all this in mind, let's go back and view the two lines of code that retrieve the Excel object:

 V := CreateOleObject('Excel.Application');
 V.Visible := True;

The first line of code asks for an object that resides inside Excel called Application. CreateOleObject retrieves an instance of the object in the form of an IDispatch interface encapsulated inside a variant called V. This variant is valuable to you because it allows you to call the methods and properties of the Excel object using a very simple syntax. For instance, you can access the Visible property of the object by simply writing V.Visible := True.

It would be mistake, however, to assume that the line of code containing the Visible property is doing the same thing as a standard Delphi line of code that looks like this:

Form1.Visible := True;

Admittedly these two lines look the same, and have exactly the same syntax. But internally, something very different is going on. In particular, if you call the Visible property of a Delphi form object then the property is changed almost instantly. Calling the Visible property of an OLE automation variant sets off a series of internal events that end up resulting in a change to the Visible property of an object inside Excel, but there are many steps that occur along the way. In particular, several methods of IDispatch such as GetIDsOfNames and Invoke must first be called behind the scenes before the call is complete.

This paper is not designed to cover the mechanisms used in dispatching a call on a variant encapsulated COM object, nor is it necessary for you to understand how it works in order to use this technology. The key point to grasp is merely that things aren't quite as simple as they at first appear. Having said all that, I will now show you how to get into this subject a bit deeper if you so desire, and if you have the source to the VCL on your machine.

To get started, copy COMObj.pas and COMObj.inc from the Delphi SourceRtlSys directory to the same directory where Excel1 is stored. Now rebuild the project so these local copies of COMObj are linked into your program. Put a breakpoint on the line V.Visible := True, and then run the program. When you get to the breakpoint, press F7 to step into the code. You will find that you are taken immediately to the VarDispInvoke method found in COMObj.pas. From there you will go to GetIDsOfNames, and finally to DispatchInvoke. What is happening here is that the appropriate methods of the IDispatch interface are being called behind the scenes by Delphi in order to "invoke" your call to Excel.

One of the lessons to be learned from this is that at bottom, there is not such a big difference between the interface technology shown in the second part of this paper and the variant based technology I am discussing here. For instance, IDispatch is an interface, and ultimately this interface must be called for the variant based technology to work. In fact, IDispatch is designed in such a way as to make the variant based technology even more complex than the standard interface technology seen in the second half of this paper. Only Delphi is able to hide that complexity from you, so that you do not need to understand it at all in order to use variants to call automation objects. (Once again, I need to emphasize that I am not giving a full explanation of this technology in this paper. If you want to really understand IDispatch, then you should check out the resources mentioned earlier in this section of the paper.)

One of the biggest consequences of calling the methods of an object off a variant is that Delphi cannot type check your code at design time. In other words, Delphi does not really know whether or not the Excel Application object has a property called Visible. It is taking you at your word when you claim this is true. In this case, that proves to be the correct thing to do. However, it would also compile without error the following code:

V.TransferMoney("From := Bill Gates", "To := Charlie Calvert", 100000);

This line of code is certainly intriguing, but the Excel Application object unfortunately does not support it. This means that a program containing it will compile and load without error, but a call to the TransferMoney property at run time will raise an exception. Both Delphi and Excel are able to handle this exception flawlessly, without destabilizing the system in any way. It is nice, however, if you can type check at design time, rather than having to wait to run time to see if all is set up correctly. The interface and dispinterface technologies covered in Part II of this paper shows how to get design time type checking of OLE objects.

I've spent the last several paragraphs describing IDispatch and variants. This is an important subject, but one that you need not understand in depth in order to use this technology. If all is not clear to you yet, you can still continue without fear. If you are hungry for more details, be patient and I will return to this subject Part II, or else you should follow the links shown earlier in this section.

After you have created an Excel Application object, you need some way to close it down. You can do this by calling its Quit method:

if not VarIsEmpty(V) then
  V.Quit;

What this code does is check to make sure that the variant V refers to something, and then it attempts to call the Quit method of the Excel application object. If V is indeed a valid pointer to such an object, then Excel will close. This code is not perfect in all cases, since V could contain a reference to something other than an Excel Application object, thereby allowing VarIsEmpty to return true, even though the call to V.Quit would fail. For instance, I could write:

V := 10;

After making this call VarIsEmpty would return false, but the call to V.Quit would obviously fail. However, in the Excel1 application, found in Listing 1, V will usually be either empty, or else pointing to a COM object. Therefore the code is reasonably robust. The key point, at any rate, is that you don't want to fail to Quit the Application object or else you can end up cluttering memory with instances of this object. Remember that Excel owns the Application object, and it will not necessarily be removed from memory just because you close your Delphi application. In other words, you should definitely call Application Quit or else repeated calls to Excel from a Delphi application will bog down your machine by draining system resources.


Server Response from: ETNASC02