ord, 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. (See sidebar: What Is a "document?"
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
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 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.Count && displays 1
? oPPT2.Presentations.Count && displays 1
? 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
; if you set oXL.Visible
, also set lShouldBeVisible
. 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 applicationswhat 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
Table 1: Save
Word changes? Pass one of these values to the Quit method to indicate whether
unsaved documents should be saved before closing Word.
Close without saving changes
Save changes before closing
Ask the user whether or not to save changes before
closing; usually not a good choice in an Automation environment.
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
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.
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
for each presentation before closing it:
FOR EACH oPresentation IN oPPT.Presentations
IF NOT oPresentation.Saved
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.")