Microsoft Office Automation with Visual FoxPro
Creating subclasses
The next step in creating wrapper classes is to create concrete subclasses of cusWrapServer. We want one subclass for each application server to be wrapped. For Office, we ll look at three subclasses: cusWord, cusExcel, and cusPowerPoint. All three inherit the custom properties and methods of cusWrapServer, as well as the code added at the abstract level.
At the subclass level, we can deal with the peculiarities of the individual products, as well as the normal differences among them. The first step is to finish defining the basic operations.
Each of the subclasses has an include file that #DEFINEs appropriate constants. Unlike other code in this book, the code that follows does not show constant definitions each time a constant is used.
The Excel and PowerPoint classes each have one additional property to add in basic operations. It s called lShouldBeVisible, and it tracks the visibility of the server. It s used in figuring out whether there s a reference to a server or not. See the section "Checking the server" later in this chapter.
Closing servers
The biggest issue in shutting down Automation servers is keeping them from displaying any messages to the user because files are unsaved. There are various ways to solve the problem the versions presented here close the files without saving them. Other alternatives are to automatically save all open documents or let you pass a parameter to CloseServer to decide whether to save open documents or prompt the user for each open document, but do it on the VFP side rather than the server side.
A couple of different approaches are needed in order to shut down without saving open documents. Word s is the easiest Quit takes a parameter that tells it to close without saving changes. Here s the CloseServer method for the cusWord class:
* cusWord.CloseServer
* Close the Word Automation server.
LOCAL lReturn
IF This.IsServerOpen()
This.oServer.Quit( wdDoNotSaveChanges )
This.oServer = .NULL.
This.oDocument = .NULL.
lReturn = .T.
ELSE
* Could tell user, but why?
* This.TellUser("Word is not open")
lReturn = .F.
ENDIF
RETURN lReturn
Excel and PowerPoint both make you do it the hard way you have to go through all the open documents and close them before you can call Quit. Here s the code for Excel s CloseServer method. PowerPoint s is pretty much the same just mentally substitute Presentation wherever you see Workbook.
* cusExcel.CloseServer
* Close the Excel Automation server.
LOCAL lReturn, oWorkbook
IF This.IsServerOpen()
* Close open workbooks first
FOR EACH oWorkbook IN This.oServer.Workbooks
This.CloseDocument( oWorkbook.Name )
ENDFOR
This.oServer.Quit()
This.oServer = .NULL.
This.oDocument = .NULL.
This.lShouldBeVisible = .F.
lReturn = .T.
ELSE
* Could tell user, but why?
* This.TellUser("Excel is not open")
lReturn = .F.
ENDIF
RETURN lReturn
Note the use of the CloseDocument method here. Without that call, CloseServer would be much longer and more complex among other things, CloseDocument is hiding the details of closing workbooks without saving changes or prompting the user.
Opening a document, part 2
The OpenDocument method (described in "Opening a document" earlier in this chapter) handles the common aspects of getting a specified document. It then calls the OpenServerDocument method to perform the server-specific portion of the task. Each subclass has code in that method. The code is pretty similar among the three classes, differing primarily in the names of the objects it deals with. Here s the PowerPoint version:
* cusPowerPoint.OpenServerDocument
* This method performs the PowerPoint-specific portion
* of opening a document. It's called by OpenDocument
* after that method checks for a valid server,
* a valid filename, and so forth.
LPARAMETER cDocument
* Attempt to open document
This.oDocument = This.oServer.Presentations.Open(cDocument)
IF UPPER(This.oDocument.FullName) <> UPPER(cDocument)
This.TellUser("Unable to open " + cDocument)
RETURN .F.
ENDIF
RETURN .T.
Closing a document
When we re ready to close a document, all kinds of questions present themselves. Which document should we close? Has the document been saved? Should it be saved? The Close methods of the Office servers have a different willingness to deal with unsaved changes without prompting the user. Regardless of the application s willingness, however, the goal of our CloseDocument methods is to avoid all prompts and carry out the user s wishes.
The CloseDocument methods we ve implemented accept three parameters, all optional. The first is the name of the document to be closed; if more than one open document could have the same name, the path should be included. The second parameter is logical and indicates whether the document is to be saved before closing it; if not, changes are discarded. The third parameter is the filename, including path, to give the document when it s saved. (You can pass the third parameter to save an existing document to a new location before closing it.)
As with many of the methods defined at this level, much of the difference between the classes is simply that you re dealing with different objects. Here s the Word version of CloseDocument:
* cusWord.CloseDocument
* Close a document in Word. If a document name is passed,
* close that one; otherwise, close the active document.
* Save the document before closing if lSaveFirst is .t.
LPARAMETERS cDocument, lSaveFirst, cFileName
* cDocument = name of document to close. Include path if more than one
* document could have the same stem.
* lSaveFirst = save document before closing?
* cFileName = filename to assign on save
LOCAL oDocToClose && the document to be closed
* Check parameters
IF NOT INLIST(VarType(cDocument), "C", "L")
This.TellUser("Specify document name or leave empty for active document")
RETURN .F.
ENDIF
IF VarType(lSaveFirst)<>"L"
This.TellUser("Specify .T. to save before closing; " + ;
".F. or omit to close without saving")
RETURN .F.
ENDIF
* Make sure Word is open and that there's at least one document.
IF NOT This.IsServerOpen()
This.TellUser("Word is not available.")
RETURN .F.
ENDIF
IF NOT This.IsDocOpen()
This.TellUser("No documents open.")
RETURN .F.
ENDIF
* Did the user specify a document?
IF VarType(cDocument) = "C"
* Check for a matching document - must use TYPE() here instead of
* VARTYPE() to avoid having the expression evaluated.
IF TYPE("This.oServer.Documents[cDocument]") = "O"
oDocToClose = This.oServer.Documents[cDocument]
ELSE
This.TellUser(cDocument + " is not open.")
RETURN .F.
ENDIF
ELSE
oDocToClose = This.oDocument
ENDIF
lSuccess = .T.
IF lSaveFirst
lSuccess = This.SaveDocument( oDocToClose, cFileName)
ENDIF
IF lSuccess
* Don't close if requested save failed
oDocToClose.Close(wdDoNotSaveChanges)
ENDIF
RETURN lSuccess
The Excel and PowerPoint versions are quite similar; for the details, see the Developer Download files available at www.hentzenwerke.com.
Creating new documents
One of the most common operations is creating a new document of whatever type the server uses. Like the final steps in opening a document, this has to be done in the subclasses because the details depend on the object hierarchy.
Both Word and Excel let you specify a template on which to base the new document, so the methods in those subclasses accept a single, optional, parameter containing the name of the template to use. If it s omitted, the default template is used. The code for the NewDocument method for those two classes is complex because it searches for the specified template to be sure it s available before attempting to create the new document. To do so, it uses a class called cusDirectoryHandler from a library named Utilities.VCX (which is included in the Developer Download files available at www.hentzenwerke.com). Here s the Excel version:
* cusExcel.NewDocument
* Create a new Excel workbook. Use a template if specified.
LPARAMETERS cTemplate
* cTemplate - the name of a template for the new workbook
LOCAL lFoundIt, cTemplatePath, oDirectoryHandler, cPath
LOCAL lSuccess, lIsServerOpen
* lFoundIt = was the specified template found somewhere?
* cTemplatePath = path to directory specified by Excel for templates
* oDirectoryHandler = object reference to directory handling object
* cPath = path to test for template
* lSuccess = return value indicating whether new document was created
* lIsServerOpen = is Excel open already?
* Check parameter
IF NOT INLIST(VarType(cTemplate), "C", "L")
This.TellUser("Specify template name or omit to use default template")
RETURN .F.
ENDIF
* Make sure Excel is open
lIsServerOpen = This.IsServerOpen()
DO CASE
CASE NOT lIsServerOpen AND NOT This.lOpenAsNeeded
This.TellUser("Excel is not available")
RETURN .F.
CASE NOT lIsServerOpen
* Open Excel
This.OpenServer()
OTHERWISE
* Excel is open and all is well
ENDCASE
IF EMPTY(cTemplate)
* If no template was specified, use the default template
* and set the flag that says we're done.
This.oDocument = This.oServer.Workbooks.Add()
lSuccess = .T.
ELSE
* Does the specified template exist?
* There are several possibilities.
* 1) If the caller provided a complete path and file,
* check it and no more.
* 2) The caller provided a relative path and file.
* We need to clean it up and check it.
* 3) The caller provided just a filename. We need to
* check for it in the appropriate template
* directories of Excel.
* The first two can be combined.
IF NOT EMPTY(JustPath(cTemplate))
* Some path was provided, so we can just go there.
cTemplate = FULLPATH(cTemplate)
lFoundIt = FILE(cTemplate)
ELSE
* First, try the user's template directory (and its subdirectories)
oDirectoryHandler = NewObject("cusDirectoryHandler","Utilities")
cTemplatePath = This.oServer.TemplatesPath
IF EMPTY(cTemplatePath)
lFoundIt = .F.
ELSE
IF FILE(ForcePath(cTemplate, cTemplatePath))
lFoundIt = .T.
cTemplate=ForcePath(cTemplate, cTemplatePath)
ELSE
* Need to go down through directory tree below here
nDirectoryCount = ;
oDirectoryHandler.BuildDirectoryTree(cTemplatePath)
nDir = 1
DO WHILE NOT lFoundIt AND nDir <= nDirectoryCount
cPath = oDirectoryHandler.GetDirectory(nDir)
IF FILE(ForcePath(cTemplate, cPath))
lFoundIt = .T.
cTemplate = ForcePath(cTemplate, cPath)
ENDIF
nDir = nDir + 1
ENDDO
oDirectoryHandler.ClearDirectoryTree()
ENDIF
* If still not found, try group template path
IF NOT lFoundIt
cTemplatePath = This.oServer.NetworkTemplatesPath
IF EMPTY(cTemplatePath)
lFoundIt = .F.
ELSE
IF FILE(ForcePath(cTemplate, cTemplatePath))
lFoundIt = .T.
cTemplate=ForcePath(cTemplate, cTemplatePath)
ELSE
* Need to go down through directory tree below here
nDirectoryCount = ;
oDirectoryHandler.BuildDirectoryTree(cTemplatePath)
nDir = 1
DO WHILE NOT lFoundIt AND nDir <= nDirectoryCount
cPath = oDirectoryHandler.GetDirectory(nDir)
IF FILE(ForcePath(cTemplate, cPath))
lFoundIt = .T.
cTemplate = ForcePath(cTemplate, cPath)
ENDIF
nDir = nDir + 1
ENDDO
ENDIF
ENDIF
ENDIF
ENDIF
* Release the directory handler. We're done with it.
RELEASE oDirectoryHandler
ENDIF
* So, did we find the template somewhere?
IF lFoundIt
* Create the new document
This.oDocument = This.oServer.Workbooks.Add( cTemplate )
lSuccess = .T.
ELSE
This.TellUser("Can't find specified template")
lSuccess = .F.
ENDIF
ENDIF
RETURN lSuccess
The Word version of NewDocument is very similar to the one shown here. The PowerPoint version is much simpler because PowerPoint doesn t offer the ability to specify a template, so it simply creates a new presentation and returns a success indicator.
Checking the server
Many of the wrapper s methods need to make sure that the server is available. The IsServerOpen method checks the server s status and returns .T. if it s open and .F. if it s not.
Two very different strategies are needed for testing the server s status because Word behaves quite differently from Excel and PowerPoint on shut down. When Word closes either by calling the Quit method or because a user shuts it down interactively, it disappears from the Task Manager and references to the Word application object generate errors. So, to determine whether the oServer property refers to a valid Word instance, we can use this code:
* cusWord.IsServerOpen
* Check whether Word is open
RETURN VarType(This.oServer)<>"O" OR TYPE("This.oServer.Name")<>"C"
PowerPoint and Excel, however, keep the Application object available until every reference to them is destroyed. In addition, even though the server has been closed, you can continue to refer to its properties and methods through the reference variable (the oServer property, in this case) without getting an error. So another strategy is called for.
Our approach has two parts. First, the CloseServer method sets oServer to .NULL. after issuing Quit, so that the server application actually closes. That handles cases where the server is closed through the wrapper class. The harder case is the one where a user closes the application interactively. To deal with that situation, the lShouldBeVisible property tracks the expected visibility of the server. It s set to .F. when the server is opened, and then set to match the server s visibility in the ShowServer and HideServer methods (see "Displaying the server" later in this chapter). When the user closes the server through the interface, the server s Visible property changes, but lShouldBeVisible does not. So IsServerOpen can check for a mismatch between those two properties. The code for Excel and PowerPoint is slightly different, due to the way they handle logical values, but the idea is identical. Here s the Excel version:
* cusExcel.IsServerOpen
* Check whether server is open.
* This method compares the custom lShouldBeVisible property to
* the server's own Visible property. If they match, the
* server is open. If not, then a user must have closed
* the server interactively. The reason this technique is needed
* is that Excel keeps the server in memory
* even after it's shut down.
LOCAL lReturn
IF IsNull(This.oServer)
* No instantiated server
lReturn = .F.
ELSE
* Compare actual Visible value to tracked visibility
IF This.oServer.Visible = This.lShouldBeVisible
* They match, so the server is open and good
lReturn = .T.
ELSE
* Visibility doesn't match. User must have shut server down
lReturn = .F.
ENDIF
ENDIF
RETURN lReturn
In order to make this work, one additional step is needed in the OpenServer method the lShouldBeVisible property must be set to .F. So, for the Excel and PowerPoint subclasses, OpenServer contains this code:
* cusExcel.OpenServer/cusPowerPoint.OpenServer
DoDefault()
* If open of server was successful,
* set tracked visibility.
IF NOT IsNull(This.oServer)
This.lShouldBeVisible = .F.
ENDIF
Checking open documents
Just as we need to know whether there s a reference to a server, many methods need to know whether there s a document open. The IsDocOpen method serves that purpose. It s coded at the subclass level because of differences in the object hierarchy, but is essentially identical in each case. Here s the PowerPoint version:
* cusPowerPoint.IsDocOpen
* Check whether there's a presentation open in PowerPoint.
* This method should always be preceded by a call to IsServerOpen
RETURN This.oServer.Presentations.Count > 1
Keeping track of the active document
A number of the methods in the wrapper class operate on the active document (whether "document" means workbook, presentation, or document for the server). It s important that the wrapper class always have a way to reference the server s active document, whichever type it is. Because the name of the server s property for that object varies depending on which server you re using, having to refer to that server property each time you need it is inconvenient.
Instead of accessing the active document through the server s property, cusWrapper has an oDocument property. To keep it up-to-date, that property s Access method contains code at the subclass level. (Since Access methods were introduced in VFP 6, this method does not easily translate to VFP 5. If you re using that version, you must keep track of the active document yourself. Though cumbersome, doing so is no problem unless the user is also allowed to interact with the Automation server instance.)
The code for oDocument_Access is quite similar for each subclass, varying only in which server property it references. Here s the Excel version of oDocument_Access:
* oDocument_Access
* Make sure the property points to the currently active document.
IF This.IsServerOpen() AND This.IsDocOpen()
This.oDocument = This.oServer.ActiveWorkbook
ELSE
This.oDocument = .NULL.
ENDIF
RETURN This.oDocument
Displaying the server
The ShowServer and HideServer methods let you display and hide the server. There are small variations between the Excel and Word versions of the two, and PowerPoint s version of ShowServer is identical to Excel s. However, when it comes to HideServer and PowerPoint, there s a big difference. That s because once you make PowerPoint visible, you can t hide it again. PowerPoint doesn t allow that.
The only difference between the Word and Excel versions of these methods is that the Excel version tracks the custom lShouldBeVisible property that it needs for the IsServerOpen method. Here are the Excel versions of both methods:
* cusExcel.ShowServer
* Show the Excel Automation session.
LOCAL lReturn
* lReturn = return value - .T. if Excel is open; .F. otherwise
* Is Excel open?
IF This.IsServerOpen()
This.oServer.Visible = .T.
This.lShouldBeVisible = .T.
lReturn = .T.
ELSE
lReturn = .F.
ENDIF
RETURN lReturn
* cusExcel.HideServer
* Hide the Excel Automation session.
* Is Excel open?
IF This.IsServerOpen()
This.oServer.Visible = .F.
This.lShouldBeVisible = .F.
ENDIF
RETURN
Copyright 2000 by Tamar E. Granor and Della Martin All Rights Reserved