Creating Excel Automation Objects
Now that you have been introduced to the topic of automating Excel, the next step is to learn something about what it means to create an OLE automation object.

The call to CreateOleObject returns a COM object called IDispatch housed inside a variant. You can pass a string to CreateOleObject specifying the name of the COM object you wished to retrieve. In this case, I have retrieved the main Excel Automation object, by passing in the string "Excel.Application'. If you are familiar with the registry, you can find this string there, and can trace that reference to the CLSID associated with the LocalServer that returns the object. If you don't know anything about CLSIDs, or about LocalServers, I wouldn't feel too concerned. The point is simply that CreateOleObject returns a COM object of your choice if you pass in the correct string. In particular, it looks up your string in the registry, finds the CLSID associated with the string, looks up the CLSID, and finds the LocalServer associated with that CLSID. The local server will be string pointing at the application that contains the object you want to retrieve. For instance, in this case, on my system, the Local Server string looks like this:

This string is copied directly from the REGEDIT.EXE application that ships with all copies of Windows. I found it in HKEY_CLASSES_ROOT\CLSID, under the guid listed next to Excel.Application. Guids are 64 byte numbers designed to uniquely identify an object.

If you want to trace out the details of this operation, and if you have the source to the VCL, you can open up COMObj.pas and find the implementation of CreateOleObject. It consists of a simple call to CoCreateInstance. CoCreateInstance is a Windows API routine that is part of the OLE specification. Its purpose is to retrieve an object from a binary file such as an executable or DLL.

The strings you pass into CreateOleObject are called ProgIDs. As you just saw, all the ProgIDs valid on your system are listed in the registry under the section HKEY_CLASSES_ROOT. (I cover this subject in more depth in the article on my web site entitled Delphi and COM.) The Delphi documentation is not the place to turn to find the ProgIDs you pass in to the various COM servers available on your system. Instead, you should turn to the documentation for the application you wish to control. For instance, Excel has extensive COM documentation in an online help file that ships with Microsoft Office called VBAXL8.HLP. (Break it down: VBA: Visual Basic for Applications, XL: Excel, 8: Version number.) If you are doing a lot of OLE Automation with Excel then you should add this file to Delphi's tools menu so you can get at it easily. For information on retrieving objects, use the Index feature in the Excel help to look up "OLE programmatic identifiers".

In the Excel online help, you will find that this spreadsheet application has three main objects you can retrieve using CreateOleObject:

These strings, and slight variations on these strings, are the only valid parameters to pass to CreateOleObject if you want to talk to Excel via COM. There are many, many more objects inside of Excel. However, these three are the only ones you can retrieve from outside of Excel using the CreateOleObject function. Once you have retrieved one of these objects, you can use it as your access to all the other objects in the Excel hierarchy. Getting at these objects is a bit like unwinding a ball of thread. You first need a handle to the ball of thread, which you get by calling CreateOleObject. Once you have a handle, you can use it to get to all the different objects inside Excel. Just keep pulling at the thread you get back from CreateOleObject and all the rest of the objects will come unraveled. This subject is explained in more depth in the next section.

Understanding Excel Automation Objects
If you are an experienced Delphi programmer, you may find OLE objects a bit confusing at first. Like standard Pascal objects, they exist inside a hierarchy, but that hierarchy, at least as it is presented to the public, is not based on inheritance. Instead, the main glue that holds the hierarchy together is the fact that you can access one particular object from another particular object.

For instance, the top member of the Excel hierarchy is called Application. Beneath it is the Workbooks object, and beneath that are the Worksheets and Charts objects:

If you want to get at the Workbooks object, then you can access it from the Application object:

If you want to get at the Worksheets object, then you can access it from the Workbooks object. And so on. In the code shown here, you would declare MyWorkbooks as a variant. In all cases, during this first part of the article, I am using variants to access the underlying Excel objects. Getting an actual interface to a Workbooks interface is covered in the second part of the article.

If you saw this hierarchy in a Delphi application, you would assume that Workbooks is a descendant of Application, and Worksheets a descendant of Workbooks. That kind of thinking is completely off center when it comes to OLE automation. The standard OOP hierarchy found in C++ and Pascal has nothing to do with OLE Automation. This is a totally different kind of hierarchy intended only to express which objects can be accessed from another object. As you will see in the second part of this paper, it may also be true that there is a valid OOP inheritance based hierarchy simultaneously implemented on this objects. However, that hierarchy is not the main one you focus on when using automation, and in fact, I think it is easiest at first to pretend that it does not exist at all.

If you want to talk about all the Worksheets and Charts in a Workbook, then you use the Sheets object. When thinking about the Sheets object, you could rewrite the hierarchy shown above as follows:

The point is that this hierarchy is meant to denote the order in which you access objects, and as such it has a somewhat more slippery structure than you would fine in a typical inheritance hierarchy. In fact, it seems that you can get at most any object from any one point in the hierarchy, so the actual structure of the hierarchy is a little dependant on your current position inside it.

You get at the Workbooks object from Application object. You get at the Sheets, Worksheets and Charts objects from the Workbooks object:

It would be untrue to say that the Application object is synonymous with the binary file Excel.exe, but it does have some things in common with this executable. For instance, the Application object is the most abstracted, the most generalized way that you have of referring to the set of available Excel automation objects. If you open up Excel and have no documents loaded, then you are looking at a visual representation of the Application object. This is not the same thing as the Application object, but it can serve as a metaphor for what the object does. It is analogous to it. It is the highest level container for accessing all of the functionality available from Excel. However, it is so generalized that it can't do much that is useful without help from other objects. But you get at those other objects by starting with the Application object. All this is equally true of Excel.exe. If you open up Excel.exe with no documents in it, then it has little use on its own, but it is still the gateway you would use to access all these documents.

The WorkBooks object contains a collection of Worksheets and Charts. A Worksheet is just a standard page from a spreadsheet, while a Chart is just a graph. The Sheets object contains both Worksheets and Charts, while the Worksheets and Charts objects contain only Worksheets or Charts. Your job as an Excel automation programmer is to start learning how to make statements like these. In other words, this is the kind of logic that underlies the Excel hierarchy of objects. As an automation programmer your job is to start to figure out how to get at one object from another object, and to understand what each object does.

Here is another way to think about what you, as an Excel automation programmer, are really trying to do. Most computer users understand how to use Excel. The automation objects discussed in this paper allow you to write code that manipulates Excel just as you would manipulate Excel with a mouse. You probably already know how to open a spreadsheet, enter data, perform calculations, and chart data. You goal as an automation programmer is to find out how to do the same things in code. You just need to know which object refers to which set of tools inside Excel. Figure that out, and figure out how to get at each of these objects given the existence of an Application object, and then you are ready to roll!

The program shown in Listing 2 provides a summary of the major points made in this section of the paper. Glance over it once, and then read on to find an explanation of how it works.

Listing 2: The Excel2 program shows how the objects in Excel are arranged hierarchically.

This application starts an instance, of Excel, then populates it with three workbooks. One of the workbooks contains a default number of worksheets, a second contains a user defined number of worksheets, and a third contains some workcharts. Over the next few paragraphs I will explain how it all works.

Take a moment to study the core of the Button1Click method:

XLApplication is a variant that contains an instance of IDispatch used for accessing the Excel Application object. As you know, there is a property of Application called Visible. If you set it to True, then Excel will appear on your screen. Once again, this is not the time or place to get into it, but COM objects support the notion of properties. These properties are very different internally from Delphi properties, but behave more or less the same.

Workbooks is a collection object. It contains a collection of workbooks. This pattern is followed over and over in Excel. The Sheets object contains a collection of sheets. The Worksheets object contains a collection of worksheets. The Charts object contains a collection of charts. Inside Word, the Paragraphs object contains a collection of paragraphs. The Words object contains a collection of words. The Tables object contains a collection of tables. And so on.

Depending on which automation server you are using, you get at member of a collection through one of four possible syntaxes. Sometimes all syntaxes are available to you, sometimes less:

You need to be conscious of the difference between a collection object and a normal object. For instance, to understand a Worksheets object, you should look up both Worksheets and Worksheet in the Excel help, to understand the Tables object you should look up both Tables and Table in the Word help.

Workbooks has a method called Add, which you use to add a workbook to a workbooks collection. COM objects support the idea of variable parameter lists. This means you can simply skip passing in parameters to a method if you want. In this case, if you call Workbooks.Add with no parameters, then you will create a workbook with some predefined number of worksheets in it. The default number is three, but you can change the number from inside of Excel if you so desire. When you are working with interfaces rather than variants, you won't be able to omit parameters. I will explain in the second part of this paper how to work with interfaces in situations such as this one, where not passing in a parameter has a special meaning.

If you want to create a new Workbook with exactly one Worksheet in it, then you call Add and pass in the constant xlWBatWorksheet. I declare this constant explicitly inside this program. In the next section of this paper I will tell you how to get a complete list of all the Excel and Word constants.

If you want to create a new workbook with exactly one chart in it, then you call Add and pass in the constant xlWBatChart.

If you then want to add one worksheet to the second workbook you created, you would write the following code:

Here is how to create a new chart:

In this case, the Add method of the Sheets object takes four parameters:

1. Before: A variant containing the sheet before which the new sheet is added.
2. After : A variant containing the sheet after which the new sheet is added.
3. Count: The number of sheets to add, with the value defaulting to one.
4. Type: One of the following constants: xlWorksheet, xlChart, xlExcel4MacroSheet, or xlExcel4IntlMacroSheet. The default value is xlWorksheet.

The first two parameters specify the location in the workbook where you want the new chart or worksheet to appear. The third parameters states how many sheets you want to add, the fourth specifies the type of sheet you want to add. Here is how the method is declared in the Microsoft docs:

In the above examples of using Add, I don't care what order the sheets are inserted, so I just omit the first two parameters by simply placing commas in my code where the parameters would be listed. If wanted to state the order, I would write something like this:

In this case the code still leaves the Before parameter blank, but it references the 2 sheet in the After parameter.

The for loop at the bottom of the example method iterates through each of the work groups, and then finds the names of each of the sheets available in each workbook, and adds them to list box. In short, the code shows how to retrieve the names of the members of a series of Workbooks, while simultaneously showing how to iterate over all their members.

Here is how to reference the number of workbooks in the application:

And here is how to count the number of sheets in a Workbook:

Here is how to find the name of a particular Worksheet or Chart in Workbook:

If you spend a little while contemplating the Button1Click method, then the logic behind the objects in Microsoft Excel should begin to come clear to you. Of course, there are additional matters to be covered, such as entering data, and creating graphs. But, as you will see, most of that material is relatively straightforward once you understand the way the Excel object hierarchy works.

One important point to make before closing this section is that it often helps to assign a specific variable to one of the sub-objects in the hierarchy. For instance, in the example shown above I declare a variant named Sheets and set it equal to the Application.Sheets object:

To my mind it is sometimes easier to mentally parse code that is written this way rather than trying to always reference a series of qualified objects such as:

Obviously, there is more overhead involved if you use the technique of storing an object reference in a separate variant. However, the technology used to implement OLE Automation on the Excel side is perhaps necessarily not particularly efficient, so you shouldn't balk at using techniques like this if you think they will help you write clear, easy to maintain code. When trying to optimize your code, remember that trips between your application and Excel are very expensive. If you can limit the number of trips you need to make, then you will save clock cycles. But once again, this whole process is innately slow, so it's a bit silly to start fretting over a few lost clock cycles that most users will never even notice.

As always, you should be particular aware of saving clock cycles when you are inside a loop. A call that takes one second to execute is easy for the user to bear if it occurs once. But put it in a loop, execute it 2000 times, and the user will hate you. A general rule of thumb is that users will happily wait up to two seconds for you to do almost anything. Longer than that and they get impatient. Two seconds is several eons in computer time, so normally you don't have to fret optimization issues. The Delphi team already did all the sweating for you. But when automating Excel or Word, you can get in trouble fairly quickly, so you may need to think about optimization in places where you wouldn't worry about it in a normal Delphi application. Remember that they call Excel automation "Visual Basic for Applications". As you will see later, these Excel classes are actually real objects, so clearly this subject doesn't have much to do with Visual Basic, but the mere presence of the word Basic costs you, by default, thousands of clock cycles!