Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX

By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.


Office Server Quirks

Automating the Office servers seems straightforward at first. But there are subtle (and not-so-subtle) differences in how they work that can trap unsuspecting developers.




Application Security Testing: An Integral Part of DevOps

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 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 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.

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 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.

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.")

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date