Word, Excel and PowerPoint all use VBA, so you would think that using Automation in one should be pretty much like using Automation in another, right? In fact, they do have a lot in common. However, there are a number of subtle differences in the way the servers operate that makes writing generic code tricky.

Some of the differences between the Office servers are obvious. Each has its own object model and the key object for each server is different. For Word, it's Documents; for Excel, Workbooks, and for PowerPoint, Presentations. Similarly, the components of each of the key objects are different. When you start drilling into Word's Document object, you find Sections and Paragraphs, and other objects that reflect the nature of documents. An Excel Workbook is composed of Worksheets and Charts. A PowerPoint Presentation is built from Slides. All these differences make sense in the context of the applications.

There are plenty of similarities in the object models. All of them have an Add method to create documents, and an Open method to retrieve existing documents. They all have Save and SaveAs methods for saving documents, and a Close method to close active documents. But even these common methods take different parameters and have different behaviors.

The first area where you'll encounter fundamental differences is in creating and destroying instances of the servers.

The behaviors described in this article apply to Office XP and Office 2000. While the examples use Visual FoxPro as the client, the behaviors are the same, regardless of client.

Starting Up and Shutting Down

The first area where you'll encounter fundamental differences is in creating and destroying instances of the servers. The servers vary in the number of instances that run at once. Only one instance of PowerPoint can run at a time. Whether you start it interactively or via Automation, any instance after the first will use the same executable rather than loading another copy. Multiple instances of Word and Excel, on the other hand, can run simultaneously.

PowerPoint's behavior here naturally extends to presentations you create. Since only one instance of the executable can run, all the presentations belong to that one instance. However, presentations are in some way associated with the object reference to the server used to create them. For example, if you run this code:

oPPT1 = CreateObject("PowerPoint.Application")
oPPT2 = CreateObject("PowerPoint.Application")
oPPT1.Presentations.Add()
? oPPT1.Presentations.Count && displays 1
? oPPT2.Presentations.Count && displays 1
oPPT1.Quit()
? oPPT2.Presentations.Count && displays 0

The presentation you created is closed when the Quit method runs. In contrast, each Word or Excel instance maintains its own collection of documents.

The servers behave differently when you close them as well. Closing Word or PowerPoint (whether interactively or by issuing the Quit method) removes the executable from memory. Closing Excel shuts down the instance, but the executable remains in memory until the variable is released or reassigned.

This difference in behavior affects code that tests whether a variable contains a valid reference to the server. (While you can control what your own code does, if you make a server visible, a user might shut it down.) For Word and PowerPoint, you can check whether the object variable is non-null, and if so, whether the Name property is character. Here's a Visual FoxPro expression to check whether a Word reference is good:

VARTYPE(oWord) = "O" AND ;
   TYPE("oWord.Name") = "C"

Analogous code works for PowerPoint. However, the analogous expression for Excel returns .T., even after Excel has been shut down, so you have to work a lot harder. The best solution I've found is to maintain an extra variable (or property), lShouldBeVisible, and keep it in synch with the Visible property of the Excel object. That is, when you make Excel visible, set lShouldBeVisible to True; if you set oXL.Visible to False, also set lShouldBeVisible to False. Then, to determine whether oXL contains a valid reference to an Excel server, use code like that in Listing 1.

There's one final difference with respect to shutting down the applications?what you have to do to prevent prompts for unsaved documents. Word's Quit method accepts a parameter to indicate whether or not to save changes to open documents before closing. The choices for this parameter are shown in Table 1.

Excel offers two approaches. The simplest is to tell it to never prompt the user for anything. To do that, set the application's DisplayAlerts property to False:

oXL.DisplayAlerts = .F.

The only downside to this approach is that all unsaved workbooks are discarded. An alternative solution is to loop through the open workbooks, closing them (with or without saving) before issuing the call to Quit:

FOR EACH oWorkbook IN oXL.Workbooks
  oWorkbook.Close()
ENDFOR

oXL.Quit()

The Workbook.Close method has several optional parameters: the first is a logical value indicating whether to save the workbook before closing it. The second parameter is the filename to use; for workbooks that aren't yet saved, you need this parameter if you pass True for the first parameter. Otherwise, one of two bad things happens. If Excel is visible, Excel prompts the user to specify a filename, just the situation you're trying to avoid. If Excel is not visible, your application hangs because Excel prompts the user anyway, but the user can't see the dialog.

Like Excel, PowerPoint's Quit method doesn't include a way to indicate what to do with unsaved presentations. However, if you do nothing, the presentations simply close with no interaction. In some cases that may be a sufficient solution.

In general, Automation code executes faster when the server is not visible.

If you want to determine what happens to unsaved PowerPoint presentations, as in Excel, you need to loop through the presentations and take action based on the value of the Saved property. However, PowerPoint's Close method doesn't let you save presentations so you have to issue Save or SaveAs for each presentation before closing it:

FOR EACH oPresentation IN oPPT.Presentations
  IF NOT oPresentation.Saved
    oPresentation.Save()
  ENDIF
  oPresentation.Close()
ENDFOR

If any of the presentations have never been saved, use the SaveAs method instead and specify the filename. While PowerPoint won't prompt the user for a name, it will decide on its own where to store the presentation and what to call it. In my tests, issuing Save on an unsaved presentation put the presentation in the WinNT\System32 directory. Issuing Save on a previously unsaved document works the same way in Excel: it doesn't display a dialog, and it picks the directory. (For Excel, though, it's "My Documents.")

Can You See Me?

In general, Automation code executes faster when the server is not visible. With Word and Excel it's easy to take advantage of this situation because you can make the server visible and invisible at will.

PowerPoint, however, prefers to be visible. In fact, once you make the server visible you can't hide it again. That is, you can't set its Visible property to False?doing so causes an error. Fortunately, it instantiates with Visible set to False so you can gain performance by waiting until the presentation is built to display it.

More importantly, some properties aren't available unless PowerPoint is visible. The most significant item is the ActivePresentation property. When PowerPoint is hidden you can access presentations only through the Presentations collection, not directly via ActivePresentation.

There's another more general issue related to visibility: activating servers and documents. In some Automation situations you want to hand control over to the user and bring his attention to a particular document. While each of the object models has an Activate method, it doesn't apply to the same objects (or their analogues) in each application. In Word and PowerPoint, the Application object has an Activate method, so you can issue a call like:

oWord.Activate()

to give Word and its active document focus. Note that calling Activate doesn't necessarily bring Word or PowerPoint to the front, but it does make it flash in the taskbar. (Consider using the BringWindowToTop API function if that's not sufficient.)

Excel's Application object doesn't have an Activate method. However, setting the Visible property to True (even if it's already True) appears to have the same effect.

I Don't Want to See You

Office applications each maintain an MRU (most recently used) list, accessible from the File menu. This is very handy for interactive users who often want to return to the last document they were editing. However, when you're automating the applications you may not want the user to be able to easily return to documents created or edited by your application.

Preventing presentations from appearing on the MRU list in PowerPoint is easy because presentations opened via Automation aren't added to the list?so you don't have to do anything. In fact, it appears that there's no way to get an automated presentation onto PowerPoint's MRU list.

Word and Excel both let you control the MRU list. In Word, the Open method has a parameter, AddToRecentFiles, which determines whether the newly opened file appears on the list. Pass True to add the file to the list and False to prevent it from appearing. If omitted, the parameter is False, so newly opened files are not listed.

Some properties aren't available unless PowerPoint is visible.

Word's SaveAs method also has an AddToRecentFiles parameter so that you can control this setting for newly created files and for files resaved with a new name. For this method, the parameter defaults to True. That is, once you save a file with SaveAs, it appears on the MRU list unless you prevent it.

By default, Excel doesn't add files opened or created via Automation to the MRU list, even when you save them. However, like Word, you can override the default behavior by passing a logical parameter to the Open or SaveAs method.

In addition to managing the MRU list when opening and closing files, both Word and Excel support a RecentFiles collection. You can explicitly add files to and remove files from the collection.

The Role of Templates

All three servers offer templates as a way to set up formatting and other features of a document. However, templates behave a little differently in each of the applications. Although the exact content of a template varies with the application, the primary features that go into a template are boilerplate contents, formatting and styles, and macros.

When you're creating a new document based on a specified template, Excel and PowerPoint simply make a copy of the template, while Word copies the boilerplate and formatting, but creates a connection to the template's macros, and optionally, to its styles. What this means in practice is that changing the code in a Word macro stored in a template can affect the documents based on that template. That's not the case for PowerPoint and Excel.

The other big difference is the mechanism for creating new templates. Word gives you two opportunities to create a new template. You can specify that the document you're creating is a template in the Add method by passing True for the optional second parameter. You can also turn a document into a template by specifying the format wdFormatTemplate (1) as the second parameter to the SaveAs method.

In Excel, you specify that a workbook is a template by passing xlTemplate (17) as the second parameter to SaveAs. Similarly, in PowerPoint, you create a template by passing ppSaveAsTemplate (5) as the second parameter to SaveAs.

Using Add-ins

Word, Excel, and PowerPoint all support add-ins, which are extensions to their core functionality. However, there are some differences, both in how you install add-ins, and in what happens when you do. In general, an add-in needs to be both registered and loaded in order to be available.

All three applications have an AddIns collection, and each of the collections has an Add method with the filename of the add-in as the first parameter. However, at this point the three applications part company.

Word's AddIns.Add method lets you both register and load the add-in at once. By default, calling the Add method loads the add-in. If you want to register it without loading it, pass False for the second, optional, parameter to Add. To unload the add-in set the Installed property of the relevant AddIn object to False.

When you create a new document based on a specified template, Excel and PowerPoint simply make a copy of the template, while Word copies the boilerplate and formatting, but creates a connection to the template's macros.

In PowerPoint the Add method registers the add-in, then you must set the AddIn's Loaded property to True to make it available.

While you can register an add-in in Excel using the Add method of the AddIns collection (when there's an open workbook) and load it by setting the Installed property to True, Excel remembers the add-in the next time it's opened, but doesn't load it properly. There's another approach that gives better results. Excel lets you register and load an add-in in one step using the Workbooks.Open method, which loads the add-in for this instance but it doesn't carry over to future executions.

In addition to the variations in how to get an add-in loaded, there's also a subtle difference in how add-ins behave while they're loading. Each of the servers supports a method that fires some code at the time the add-in is loaded. The exact name of this method varies with the application: Word calls it AutoExec, while Excel and PowerPoint call it Auto_Open. More important than the name of the method, though, is that Word's AutoExec method doesn't fire when Word is running as an Automation server, but only when Word was started interactively. This means that you can't count on code in the AutoExec method of an add-in to run when you use Word via Automation, or when you load an add-in in an automated Word instance.

Summing Up

Despite the fact that Office is a product suite, there are many differences between the various servers. The differences mean that you can't just write Automation code that works with one of the servers and assume it'll work with the others. This article should get you past some of the issues you're most likely to run into, but expect to find others as your Automation code gets more complex.

Contact info:

Tamar E. Granor

8201 Cedar Road

Elkins Park, PA 19027

Phone: 215-635-1958

Fax: 215-635-2234

Email: tamar@thegranors.com