Microsoft Office Automation with Visual FoxPro
Error-handling strategies
In any programming environment, there are two ways to handle errors: proactively and reactively. The proactive approach is to write your code so you prevent errors from happening. For example, you can avoid "Invalid Index" errors by checking oExcel.Workbooks.Count to ensure that oExcel.Workbooks[43] is available. The reactive approach is to let the ON ERROR procedure and/or the Form or Object s Error method handle the error. A solid error-handling strategy uses both methods to ensure errors are handled gracefully.
Exactly how much of the error-handling strategies should be proactive or reactive is a personal preference. In fact, "personal preference" should really be stated as "intensely held beliefs defended with the tenacity of a pit-bull." We ve both had heated conversations with others about how to handle errors, and we re not going to go into a debate about the pros and cons of specific implementations of error handlers.
We will say, though, that because all Automation errors are funneled through just a few error numbers, this challenge requires some creative problem solving. There are some techniques that you can mull over and decide how to implement in your existing error handling scheme. We ll also insist on some proactive, preventive approaches.
ON ERROR and the Error method
Chances are, you are adding Automation code to an existing application, or using an existing application framework. You can choose to handle Automation errors in the procedure called by ON ERROR, which works just fine, especially if Automation code is sprinkled throughout your application.
If your application isolates the Automation code, then a localized error handler may be more appropriate. In the JFAST application, the Automation code is launched from one form, and nearly all the code launched there is Automation code (as opposed to VFP statements). The error handler works nicely in the form s Error method, which overrides the setting of ON ERROR. Another logical place for error handling is in the Error method of the wrapper class that you should use (see Chapter 15, "Wrapping Up the Servers"). Just remember that the wrapper class can t trap for errors in code that runs outside the wrapper class.
If you re working in a team of developers, before you go cobbling your application s ON ERROR routines with the latest and greatest Automation error handling, try isolating it and perfecting it in the form s Error method. That way, you can perfect the error handling routines while testing your form, then make one change to the application s error handler. That way, you can minimize the number of times you make changes to the application s error code (and the number of times your team members show up at your desk demanding to know why you ve "fixed" it).
Listing 1 (ErrorMethod.PRG in the Developer Download files available at www.hentzenwerke.com) shows some sample code to use as a starting point. Undoubtedly, you ll find other ways to handle certain flavors of 1426 and 1429 in your application. This is a good starting point on which to expand as you become more familiar with the kinds of errors your application experiences. This simple skeleton builds a string to indicate what has happened, and displays it to the user. The last bit of the method closes down the code that is running you ll want to comment out that code during development, replacing it with a SUSPEND or returning you to your code (like an Ignore) to find the next error.
Listing 1. A sample format for an Automation error handler, residing in the form s Error method. This method is available as ErrorMethod.PRG in the Developer Download files.
* ErrorMethod.PRG
LPARAMETERS nError, cMethod, nLine
LOCAL ErrorArray[1], AppName, Instructions, oWkBk
m.ErrorMessage = MESSAGE()
#DEFINE CR CHR(13)
#DEFINE wdDoNotSaveChanges 0
* Grab the info in the error array--useful only for OLE errors 1426-1429.
= AERROR(ErrorArray)
* Build a user-friendly error message.
DO CASE
CASE nError = 1420 && OLE object is invalid or corrupted.
m.Instructions = "Try reinstalling Office."
CASE nError = 1421 && Cannot activate the OLE server.
m.Instructions = "Try reinstalling Office"
CASE nError = 1422 && Error saving the OLE object.
m.Instructions = "Report this error."
CASE nError = 1423 && Error creating the OLE object.
m.Instructions = "Report this error."
CASE nError = 1424 && Error copying the OLE object to Clipboard.
m.Instructions = "Report this error."
CASE nError = 1426 && OLE error code 0x"name".
* This is a good place to check for ErrorArray[3], and determine what
* to do based on LEFT(ErrorArray[3], 8), the error code. One example
* is shown below.
IF LEFT(ErrorArray[3], 8) = "800706ba" && The RPC server is unavailable
m.Instructions = "The server was shut down."
ELSE
m.Instructions = "Report this error"
ENDIF
CASE INLIST(nError, 1427, 1428, 1429 )
* 1427 = OLE IDispatch exception code "name".
* 1428 = OLE IDispatch exception code "number"
&& from "server": "name".
* 1429 = "OLE error"
* Don't forget that you can query the following:
* ErrorArray[3], the text of the OLE message
* ErrorArray[5], the application's Help File
* ErrorArray[6], the Help context ID
* ErrorArray[7], the OLE 2.0 exception number - this is particularly
* useful for handling individual exceptions.
IF NOT ISNULL(ErrorArray[4])
m.AppName = "Problem with the application: " + ErrorArray[4] + CR
ELSE
m.AppName = ""
ENDIF
m.Instructions = AppName
CASE nError = 1440 && OLE exception error "name". OLE object may be corrupt.
m.Instructions = "Report this error."
OTHERWISE
m.Instructions = "Report this error."
ENDCASE
= MessageBox("UNEXPECTED AUTOMATION ERROR:" + CR + CR + ;
m.ErrorMessage + CR + ;
"Error #: " + TRANSFORM(nError, '9999') + " in " + cMethod + CR +;
"at line " + TRANSFORM(nLine, '9999999') + CR + CR + ;
m.Instructions, "AUTOMATION ERROR")
* Our example shuts down open objects. Your application may need
* a different strategy, such as closing the module, closing the app, or
* issuing an Ignore, Retry, Resume message box. Modify this to fit your
* application
IF TYPE("oPowerPoint") = "O" AND NOT ISNULL(oPowerPoint)
oPowerPoint.Quit()
ENDIF
IF TYPE("oWord") = "O" AND NOT ISNULL(oWord)
oWord.Quit( wdDoNotSaveChanges )
ENDIF
IF TYPE("oExcel") = "O" AND NOT ISNULL(oExcel)
FOR EACH oWkBk IN oExcel.Workbooks
* Insert your favorite method of saving or closing
* each workbook, so Excel quits without a message
NEXT oWkBk
oExcel.Quit()
ENDIF
* Remove the variables - modify to fit your application
RELEASE oPowerPoint, ; && and any other references to PowerPoint objects
oExcel, ; && and any other references to Excel objects
oWord && and any other references to Word objects
ThisForm.Release()
RETURN TO MASTER
Why do we shut everything down in this error handler? With so many different kinds of errors funneled through 1426 1429, we don t know if this error will affect only the one line (for example, unable to set the bold property), or if it will cause a cascade of errors (as in the case of the server disconnecting). This is the safest method to prevent cascading errors. As you develop your application, you ll find specific OLE error numbers (1427 1429) or error codes (1426) that you can handle within this framework. For example, in some situations such as batch processing, it may be sufficient to log certain errors and proceed.
Preventing errors
Instead of reacting to errors that occur, it makes sense to try to prevent them. Many of the errors can be prevented, such as checking the Count property of a collection before you specify an index, to ensure that the index isn t out of range.
Another frequently encountered error is to assume that the server application knows about the FoxPro environment. Most developers have relied on the settings of SET PATH and SET DEFAULT to find files. Anytime you find yourself specifying a filename that s in the default directory, be sure to prefix the filename with SYS(5) + SYS(2003), so the server can find it. If it s not in the default directory, be sure to specify the drive and path. Using FullPath() is a quick way to get the path to a file, although more than one same-named file in the path can caused some surprising results.
The server doesn t have a clue about the setting of SET SAFETY, either. Worse yet, if you automate all four Office applications, you ll find that each one behaves differently when you attempt to overwrite a file or exit the application with a document in an unsaved state. Check the "Basics" chapters for each server to ensure that your application does what you want it to do.
If your servers routinely don t open, check the status of SET OLEOBJECT, which must be ON. When SET OLEOBJECT is ON, it tells VFP to search the registry for the server. If it s set to OFF, it prevents VFP from searching the registry, which is where all the Automation servers are found.
Ensuring the server exists
Your application needs to make sure that the user has Office installed on their machine, and that it s properly registered. FoxPro locates COM (hence Automation) objects through the registry. A quick peek at the registry can tell you whether the server has been installed.
You can tell it s been installed. But what if the user is short on disk space, and decides to free up some space by deleting the whole series of Office directories, rather than uninstalling Office? The registry entries are intact, but they point to a non-existent file. You need to check that, too.
Did you know that a user can have the Office applications on their machines, and they work successfully from the desktop icons and the Start menu, but Automation can t find them? Yes, it can happen, and you ll spend weeks trying to figure this one out. The problem is in the registry. Della found out the hard way when a user decided he didn t like the default location of the Office files, and manually moved the directory, then changed the icons and the Start menu to reflect the new location. The problem was that he didn t change the registry (it wasn t his fault he didn t even know what a registry was). The error message, "Excel is not properly installed on this machine; please reinstall it," was terribly confusing to him, since he was using Excel, so it must be properly installed. Another reason to check for the file that the registry thinks it s supposed to find.
One more gotcha: if you re using Office 97, you want SR-1 applied at a minimum (there is also SR-2 and SR-2B), because it fixes some problems with Automation. Check the version numbers to ensure your user has the proper version. The good news is that it isn t hard to check the registry. A few API functions are used that access the registry. If you re thinking APIs are hard to use, get over it! First, though, a few words about the registry.
Registry 101
The registry is a hierarchical list, made up of keys. Figure 4 shows the Registry Editor, with the six basic keys (Windows 98 is shown). Opening a key reveals a series of subkeys. Figure 5 shows the HKEY_CLASSES_ROOT key opened to reveal its first level of subkeys. If you look in the Registry Editor s Help file, you won t find a reference to the term "subkey." The distinction between key and subkey is helpful when passing parameters to the API functions.
Figure 4. The Registry Editor open to show the registry keys. The registry keys vary with each version of Windows; Windows 98 is shown here.
Figure 5. The Registry Editor shows the HKEY_CLASSES_ROOT key opened, with the subkeys displayed. The pane on the right shows the values associated with the subkey.
Each subkey has one or more values associated with it. A value is made up of two parts: the name and the data. The name is simply a character string describing the contents of the data. Figure 5 shows the .323 subkey with two values: the "(Default)" value of "h323file," and the "Content Type" value of "text/h323."
As we said before, the registry is a hierarchical list of keys. The main key contains subkeys, subkeys can have subkeys, and so on. Many programs have a series of keys sprinkled liberally throughout the registry. There is a series of keys for each Office application used in Automation tasks.
Automation uses the class name key to find the server information. Excel s class name, "Excel.Application," is shown in Figure 6. The class name key has a subkey, CLSID, that contains the GUID, or Globally Unique IDentifier. GUIDs, like Excel s, shown in the right pane of Figure 6, are generated by the application developer (in this case, Microsoft). The registry uses these GUIDs to track the programs. This number ensures each application is uniquely represented in the registry.
Figure 6. The class name key for Excel, "Excel.Application." Automation looks up the class name key, finds its CLSID subkey, and looks for its default value, which contains the GUID. This is used for further registry lookups.
Once we obtain the CLSID key s data value, we can use that to find the key that has a value containing the fully qualified filename. The hierarchy of keys is HKEY_CLASSES_ROOT, CLSID, the GUID (the globally unique identifier), and finally LocalServer32. Its one and only value is the fully qualified EXE name. Figure 7 shows the Registry Editor opened to this key for Excel.
Now you know what to look for. Now you need to know how to do it. A quick primer on APIs is in order.
Figure 7. Some registry keys for Excel. The left pane shows the hierarchy of the subkeys. The parent keys that are off the top of the display are HKEY_CLASSES_ROOT\CLSID. The default value for the LocalServer32 key is the executable called by Automation calls.
API 101
API stands for Application Programming Interface. Windows comes with a number of DLLs, which contain functions that access all kinds of system-level information. This collection of functions comprises the Windows API.
To use one of these API functions, you must first register the function with the DECLARE command. The DECLARE command takes a number of arguments. The first is an optional argument that indicates the data type of the value returned by the function. The options are SHORT, INTEGER, SINGLE, DOUBLE, LONG, and STRING. The next argument is the function name to register. This argument is case-sensitive if you re getting errors, be sure to check the case, as most of the Windows API calls are mixed case. The third argument tells the library name. You can specify the DLL, or, if it s a standard Win32API call (usually those in Kernel, User, and GDI DLLs) , you can specify the keyword WIN32API. Be sure to include the required keyword IN. In the event that the function name collides with an existing FoxPro keyword, you can add an optional phrase AS <aliasname>, so you can rename the function.
Lastly, you need to specify the data types of each of the function s parameters. Pass a comma-separated list of data types. Your choices are INTEGER, SINGLE, DOUBLE, LONG, and STRING. If the parameter is to be passed by reference (where the function will change the value of the variable), you must add an @ sign after the data type (and before the comma). You also have to use the @ when you call the function, to pass by reference, but if you omit the @ in the DECLARE, you won t be able to pass by reference. You may also add an optional name after the @; it s not used at all by FoxPro, except to document it, but that s a good enough reason to do it.
The RegOpenKey function is used to open a key. Once a key is opened, you can query its values. The DECLARE command for the RegOpenKey function is:
DECLARE RegOpenKey IN ADVAPI32.DLL ;
INTEGER Key, STRING Subkey, INTEGER @ ReturnedHandle
You can now use this function like any other function, as in:
hOpenKey = 0
= RegOpenKey(2147483648, ;
"CLSID\{000209FF-0000-0000-C000-000000000046}\LocalServer32", ;
@hOpenKey)
Just be sure that you have a variable defined, of the proper type, before you pass a variable by reference. The variable, hOpenKey, should be initialized to 0 before calling, as in the preceding example.
API functions aren t all that difficult to use. The hard part is knowing how to find the functions that are available. Fortunately, the Visual FoxPro team provides wrapper classes (containing full source code) for the API functions used to access the registry. In VFP 6, a FoxPro Foundation Class called Registry.VCX is available. In VFP 5, you ll find a sample file called Registry.PRG, containing the same code as the VFP 6 VCX, except defined in code.
By instantiating the Registry class to a variable, oRegistry, you can take advantage of the wrapper classes. In VFP 6, use the NewObject() function, which works like CreateObject(), except you specify what VCX the class is in. The VCX does not have to be opened or in the path for NewObject() to create the instance of the object:
oRegistry = NewObject("Registry", HOME() + "FFC\Registry")
In VFP 5, you have to set the procedure file to Registry.PRG, then instantiate the class:
SET PROCEDURE TO \vfp5\samples\classes\registry.prg ADDITIVE
oRegistry = CreateObject("Registry")
The Registry class is a custom class with many methods to take the pain out of using the API functions. It performs the DECLAREs for all the functions (regardless of how many functions you intend to use), and then uses properties to store many of the values passed by reference. To open a key, you pass by reference a variable that has been initialized to 0. The API sets that variable to the handle. Then, you use that handle in the RegEnumValue() function, also passing it blank variables to fill. The Registry class OpenKey method stores that handle to a property for use by the EnumKeyValues method (or any other method that relies on the opened key). This lets you concentrate on getting the information out of the registry rather than trying to manage hordes of variables.
Digging in
We ve established what to look for, where to look for it, and how to find it; now let s put it together. Once a key is opened with the Registry class s OpenKey method, you can obtain a list of the key s values with EnumKeyValues.
Once you have opened a key, you can list its values to an array. EnumKeyValues takes one parameter, a blank array passed by reference. The method populates your array with a list of all the values, putting the name in column 1 and the data in column 2. While a key can contain many values, in both the keys you want to check, only one value is returned. The first position of the array contains the key name "(Default)," and the second position contains the data you re looking for. These two Registry class functions are demonstrated in Listing 2, which is included as CheckServer.PRG in the Developer Download files available at www.hentzenwerke.com. It s set up to take one of the Office server class names as a parameter, as in:
? CheckServer("Word.Application")
The function returns a logical indicating whether the server is installed. Note that once we find the value of the key, we need to strip off some trailing characters (see Figure 7 for an example of the string). Then we check for the existence of the file.
Listing 2. Pass the class name of the application to find out whether the Office application is registered and on the disk. Try changing the name of Word.EXE to test it; be sure to rename the EXE back when you re done testing.
LPARAMETER cServerName
LOCAL oRegistry, cClassID, cEXEName, lEXEExists, ;
aClassIDValues, aClassIDValues, aServerNameValues
IF VERSION() = "Visual FoxPro 06"
oRegistry = NewObject("Registry", HOME() + "FFC\Registry")
ELSE
SET PROCEDURE TO HOME() + "samples\classes\registry.prg" ADDITIVE
oRegistry = CreateObject("Registry")
ENDIF
lEXEExists = .F.
DECLARE aClassIDValues[1], aServerNameValues[1]
WITH oRegistry
* Find the CLSID of the server. First, look for
* the Class's Key.
IF .OpenKey(cServerName + "\CLSID") = 0
* The Class's Key is open, now enumerate its values
.EnumKeyValues(@aClassIDValues)
* The data portion of the first (only) value returned
* is the CLSID. Find the LocalServer32 key for the CLSID
IF .OpenKey("CLSID\" + aClassIDValues[1,2] + "\LocalServer32") = 0
* Enumerate the LocalServer32 values
.EnumKeyValues(@aServerNameValues)
* The EXE file is stored in the first (only) data value returned.
cEXEName = aServerNameValues[2]
* The value that's returned may have " -Automation" or " /Automation" or
* " /AUTOMATION" & other trailing stuff at the end. Strip it off.
IF "AUTO" $ UPPER(cEXEName)
cEXEName = LEFT(cEXEName, ATC("AUTO", UPPER(cEXEName)) - 2)
ENDIF
* Verify that the file exists
lEXEExists = FILE(cEXEName)
ENDIF
ENDIF
ENDWITH
RETURN lEXEExists
Office versions
Since both the 97 and 2000 versions of each Office application share the same GUID, the code only verifies that one of the versions exists; it doesn t tell you which one (unless you specifically check for a specific version in the class name, as in "Word.Application.9"). VFP 6 introduces the AGetFileVersion() function, which lets you check on the version information stamped into the file. It takes two parameters the first is an array to populate with the version information, and the second is the filename from which to extract the version information. The function returns the number of rows in the resulting array, which is 15 if it s successful. Of the 15 elements in the array, we re interested in the fourth, the file version (see Help for the other 14 items). Element 4 is a character string, which looks something like "9.0.2719".
Listing 2 pulls out the filename into the variable cEXEName; you may want to append the following code segment to the end of the code, and pass a version parameter by reference to have the CheckServer() function check the server and version.
* Verify that this is the path to Excel on your machine, or use the
* variable of the same name in Listing 2.
cEXEName = "C:\Program Files\Microsoft Office\Office\Excel.EXE"
nRows = AGetFileVersion(aVersionInfo, cEXEName)
cVersion = aVersionInfo[4]
Now it s your choice what to do with the character string, whether to convert it to a number or leave it as a character for your comparisons. Keep in mind that version 9 is Office 2000, and version 8 is Office 97.
AGetFileVersion() is only available in VFP 6. VFP 5 has a GetFileVersion() function in FoxTools. It takes the same two parameters, but their order is reversed. Also, the array must already exist and be defined to at least 12 rows and one column before you pass it by reference. Here s the code rewritten for VFP 5:
cExeName = "C:\Program Files\Microsoft Office\Office\Excel.EXE"
SET LIBRARY TO FOXTOOLS.FLL
DECLARE aVersionInfo[12]
= GetFileVersion(cExeName, @aVersionInfo)
cVersion = aVersionInfo[4]
Copyright 2000 by Tamar E. Granor and Della Martin All Rights Reserved