3.1 Managing servers | Microsoft Office Automation with Visual FoxPro

Managing servers

The first step with any server is getting a reference. There are two possibilities for an Automation server: you can create a new instance, or you can get a reference to an existing server, if one exists. There are also two VFP functions that you can use, CreateObject() and GetObject(), but they don t map exactly to the two techniques.

CreateObject() always creates a new instance of the server and returns a reference to it. Here s the syntax for creating Automation objects:

oServer = CreateObject( cServerClass )

cServerClass is the appropriate name for the main class of the Automation server. For the Office applications, it s "<appname>.Application" where <appname> is "Word" or "Excel" or whatever. For example, to open PowerPoint, issue this command:

oPowerPoint = CreateObject( "PowerPoint.Application" )

Note that the syntax for creating Automation objects is almost identical to that for creating native objects. The only difference is the class of the object created. This is polymorphism (one of the pillars of OOP) at work.

If the server is already open and you d like to use the existing instance, you can use the GetObject() function instead. GetObject() takes two parameters. For this use, omit the first parameter and pass the server class as the second. Here s the syntax:

oServer = GetObject( , cServerClass )

If the server is already open, GetObject() finds it and returns a reference to that instance. However, if the server is not open, this version of the function generates an error.

For example, to attach to an open instance of Excel, use this command:

oExcel = GetObject( , "Excel.Application")

There s another way to start a server by passing GetObject() the name of a file and letting it figure out which server to use:

oDocument = GetObject( cFileName )

VFP looks up the file s extension in the registry to figure out what application it belongs to, opens that application, then opens the file; it works pretty much as if you d double-clicked the file in Windows Explorer, except that you get an object reference to the file as a "document," so that you can manipulate it as an object. This example opens the file "C:\Documents\MyFile.DOC" in Word and returns a reference to it:

oDocument = GetObject( "C:\Documents\MyFile.DOC" )

In some situations, the filename may not be sufficient to determine what application to open or what to make of the file. In that case, you can also pass the name of the class within the server. For example, if the file has a TXT extension, but you want Word to open it as a document, pass "Word.Document" as the second parameter, like this:

oDocument = GetObject( "Example.TXT", "Word.Document" )

However, you can t just pass the file type automatically. Most of the time, either VFP or the server application chokes when it receives the second parameter unnecessarily.

We should point out that there s a third VFP function in this family, CreateObjectEX(). It s used for creating objects on a different machine and is part of DCOM. Since this book doesn t cover DCOM, we don t discuss it here.

Displaying the Office servers

When you instantiate an Automation server, by default it s invisible. It doesn t show up on the taskbar. In Windows NT, it s shown on the Processes page of Task Manager, but not on the Applications page. It does show in Windows 95/98 s Close Program dialog. It s generally a good thing that it keeps itself somewhat hidden. Often, you re doing something behind the scenes and there s no reason for a user to see it happening. However, while debugging and in some other circumstances, you may want to make the Automation process visible.

Why not just make the server application visible all the time? Speed. Not surprisingly, manipulating documents is faster when you can t see them. It s also tidier although watching a spreadsheet or presentation being built is pretty cool the first few times, after a while, users are likely to get tired of watching.

This is one area where Outlook is the odd man out. These commands apply to Word, Excel, and PowerPoint (and, for that matter, to Visual FoxPro when it s used as a server), but not to Outlook. To make the Automation server visible, set its Visible property to .T.; set Visible to .F. to turn it off. (Actually, in PowerPoint, you can set Visible to .T., but setting it to .F. generates an error message. Word and Excel give you complete control over their visibility.) Outlook doesn t have an analogous property.

The WindowState property of the Application object determines whether the application is minimized, maximized, or "normal," meaning some user-determined size. Manipulating WindowState when the application is invisible generally makes it visible (at least to the extent of showing on the taskbar). Each of the applications has a set of constants for the three possible values of WindowState. For example, for Excel, you can make these definitions:

#DEFINE xlMaximized -4137

#DEFINE xlMinimized -4140

#DEFINE xlNormal -4143

Word needs the following:

#DEFINE wdWindowStateNormal 0

#DEFINE wdWindowStateMaximize 1

#DEFINE wdWindowStateMinimize 2

The Application object of all three applications has Left and Top properties that determine where it s located on the screen, as well as Height and Width properties. You can manipulate these, unless the application is maximized. The following code (SetSize.PRG in the Developer Download files available at www.hentzenwerke.com) opens Word, sets the application window to normal, positions it a little below the upper-left corner of the screen, and then makes it visible.

#DEFINE wdWindowStateNormal 0

LOCAL nScreenHeight, nScreenWidth

LOCAL nWindowHeight, nWindowWidth

* Compute sizes

nScreenHeight = SYSMETRIC(2)

nScreenWidth = SYSMETRIC(1)

* Make it two-thirds the size of the screen in each dimension.

nWindowHeight = INT(.67 * nScreenHeight)

nWindowWidth = INT(.67 * nScreenWidth)

RELEASE ALL LIKE o*

PUBLIC oWord

oWord = CreateObject( "Word.Application" )

WITH oWord

.WindowState = wdWindowStateNormal

.Height = .PixelsToPoints( nWindowHeight, .T. )

.Width = .PixelsToPoints( nWindowWidth, .F. )

.Top = 10

.Left = 10

.Visible = .T.

ENDWITH

RETURN

As the example indicates, Height, Width, Top, and Left are measured in points. Word provides a number of methods for converting other measurements into points (including, in Word 2000, the PixelsToPoints method used in the example). The other applications offer fewer such methods; in fact, neither Excel nor PowerPoint includes PixelsToPoints. The conversion factor between pixels and points is about 0.75.

Unless you re doing very precise work, you can probably live with that conversion factor, so in Excel and PowerPoint, just define your own conversion, like this:

#DEFINE autoPixelsToPoints .75

You can handle converting between inches and points in the same way. There are 72 points to an inch. Define your own constant for that task, like this:

#DEFINE autoInToPts 72

Even when you re automating Word, there s some argument that you re better off doing your own conversions in VFP than using Word s built-in methods. Each call to Word is expensive. In our tests, arithmetic in VFP was about 100 times faster than calling Word s conversion methods.

Are we there yet?

All three applications start with no document open and no document window when you call CreateObject(), but they behave differently when GetObject() is used to open a specific file.

In Word, once GetObject() is through, there s a document window containing the specified document. Just set Visible to .T. for the Application object, and the document window shows:

oDocument.Application.Visible = .T. && this is needed for any of the apps

&& but change the variable appropriately

PowerPoint and Excel are different. Setting Visible to .T. isn t enough. In PowerPoint, you need to call the NewWindow method to provide a document window for the specified presentation:

oPresentation.NewWindow()

Excel uses yet another approach to the same problem. There is a document window; you just can t see it. Call the Activate method for the first window in the Windows collection:

oWorkbook.Windows[1].Activate()