Microsoft Office Automation with Visual FoxPro

This book grew out of another book. In 1995, when FoxPro grew into Visual FoxPro, Tamar, together with Ted Roche, the technical editor of this book, wrote a book called Hacker’s Guide to Visual FoxPro 3.0 (Addison-Wesley). The bulk of that book, nearly 700 of its 900-some pages, was an alphabetic reference to every command, function, property, event, method, and system variable in Visual FoxPro. Oddly enough, Tamar and Ted didn’t just sit down and create a single Word document starting from "A" and working their way through to "Z." In fact, about 600 documents went into that section of the book.

When the writing was done and the smoke cleared, Ted had a brilliant idea. (Actually, he had the idea well before the writing was done.) VFP 3 was the first version of the product to support Automation. He wrote some code to take those 600 documents and put them together in the right order to create the reference section of the Hacker’s Guide.

In 1998, Tamar and Ted were at it again with an updated and expanded version called Hacker’s Guide to Visual FoxPro 6.0 (Hentzenwerke). This time, Tamar took on the task of assembling the book while Ted used Automation to turn the whole thing into an HTML Help file. With the new material from VFP 5 and 6, there weren’t 600 documents involved; there were more than 800.

In addition, changes in the way the book was managed meant that, before producing the final version, a lot of clean-up work had to be done. For example, Microsoft had changed the name of the product during the beta process. (Visual FoxPro 98 became Visual FoxPro 6.) The copy editor had done a wonderful job of finding inconsistencies in terminology, catching things like "textbox" vs. "text box," but some sections edited early in the process needed to be corrected. The biggest issue of all came very late in the game when a decision was made to make the book 8.5" x 11" instead of 7" x 9"—this meant that margins had to be changed, tables had to be resized, and many other changes were needed to every one of those 800+ documents.

Handling all of these changes by hand would have taken months and the book would have been hopelessly late. Automation to the rescue. Tamar wrote VFP code to open each document in Word, do the necessary processing, and save it as a new document. After all the processing was done, an updated version of Ted’s original assembly code created the reference section.

Along the way, while falling in love with Automation, Tamar beat her head against the wall regularly. Brute force was the order of the day. The Word documentation was helpful, as were the people on CompuServe’s MSWord forum. But nowhere was there a real resource for someone writing Automation code like this, especially someone writing it from Visual FoxPro.

While Tamar and Ted were having adventures with books, Della was working on perhaps the best-known FoxPro application in the world. The Joint Flow and Analysis System for Transportation (JFAST) is a logistics application written for the US Department of Defense. It aids in planning the movement of people and materiel—give it a long list of what you need (from tanks to troops to canteens), along with where it is now, where it needs to be, and when it needs to get there, and it produces a detailed schedule to load the people and materiel onto ships and aircraft to get it to its destination. (That sentence is far too bald a statement of what JFAST does. Watching this application in action is an astonishing experience.)

Della’s portion of the tool creates reports and briefings that explain the recommended plan. Automation is used to produce PowerPoint briefings, suitable for presenting the plan to high ranking generals, and even the Commander In Chief. Automation is also used to generate a Word document that analyzes the strengths and weakness of the plan. Other modules were developed to automate Microsoft Project and Microsoft Schedule+ to provide still more analytical views.

This all came about when Brian Jones, the genius behind JFAST (and its project manager), came to Della’s office with an idea. He knew it was possible to take data from FoxPro tables and put it into another program and that Automation (actually, DDE was the reigning technology at the time) was key. He described what he wanted, and Della set off to accomplish her mission.

First came AutoBrief, the automated briefing system. The idea is that the user can select a series of slides from a master list, and push the Start button. AutoBrief generates slides based on the current data in the FoxPro tables. In AutoBrief’s infancy, Office 95 was the current version. Documentation on the Office object models was sparse; as for example code, what example code? Even VB examples were hard to come by. But with a lot of perseverance, Della managed to conquer the PowerPoint object and the Excel model as well—Excel is used to generate the many graphs that are needed in the presentation.

Then came AutoAnalysis, which produces a Word document containing lists, tables, charts, and even verbal analysis. Again, using limited documentation, Della’s code produces a very professional document at the touch of a button, including table of contents, index, headers, footers, lists, and Excel graphs and DataMaps. At least Word and Excel had a macro recorder, making it much easier to learn the object model; PowerPoint 95 didn’t have a macro recorder at all. (See Chapter 2, "The Office Servers," to find out just how useful the macro recorders are.)

Then Office 97 shipped. The entire object model for Excel, Word, and PowerPoint was radically changed. The change was for the better because each application’s object model was much more consistent with the other Office applications (that is, had better polymorphism). Regardless of how wonderful the changes were, though, the code was still broken. Big time. It was then that Della realized the value of writing wrapper code. Big time. Fortunately, the wrapper code was a little easier to write, since Office 97 had much better documentation, in both quantity and quality. Error handling became an issue, as more and more users relied heavily on this Automation feature, illustrating the need to check for broken registries, improper installations of Office, and other gotchas that FoxPro developers aren’t used to checking.

Della’s been developing Automation code in FoxPro on a daily basis since 1995. She still wishes for a good Automation resource for the FoxPro developer. So she jumped at the chance to write this book.

In this book, we’ll try to save you from the pain we’ve already been through. We’ll do it in a couple of ways. First, we’ll share the key pieces of automating the Office applications, the things we think pretty much anyone working with them needs to know. In fact, we think most of this is relevant whether you’re automating Office from Visual FoxPro, Visual Basic, Visual C++, or Visual SquidPro.

Second, we’ll tell you everything we know about how to find out more about the Office Automation servers. We’ll share our tricks for figuring out which object you need to talk to, what method to call, and which property really matters.

Third, we’ll tell you what tripped us up. We’ll tell you about the methods that seemed intuitively obvious to us, but in reality, were just the wrong thing.

This book was written with Visual FoxPro 6.0 (actually VFP 6.0 Service Pack 3, but it applies to the original version as well) and with Microsoft Office 2000. Almost everything here applies as well to Office 97. When there are serious version differences, you’ll find an icon in the text to warn you.

On the VFP side, it’s a little trickier. Automation worked quite well in VFP 5, and the chances are good that almost everything here works there, too, but we haven’t tested most of it there. As we’re writing this book, we hope to see early betas for the next version of Visual FoxPro soon. Everything in our past experience tells us that all of this will work there, too, and in fact will work better, that is, that any problems we might have in VFP 6 are likely to get fixed in VFP 7 (or whatever they decide to call it).

Because we’re FoxPro programmers, and because every other book on the market uses Visual Basic for examples, the examples in this book are written in VFP. What’s amazing, though, is that you have to really look at the code examples to realize that. In this brave new world of Automation and interoperability, VFP code and VB code don’t look as different as they used to. But more about that later.

The major examples in this book use the TasTrade database that comes with Visual FoxPro. If you’ve worked with another Microsoft product (say, Access), you may find the actual data familiar. That’s because it is—VFP’s TasTrade data is pretty much the same as Access’s Northwinds database. There are some differences, but the customers, employees, and data within look pretty similar.

In VFP 6 and later, the system variable _SAMPLES points to the directory where sample programs and data were installed. The examples in this book use this variable to find the TasTrade data. To install the sample data, you must perform the MSDN portion of the VFP installation, which also installs the Help file.

If you’re using an earlier version of VFP, replace references to _SAMPLES with HOME()+"\SAMPLES\". In those versions, the samples were installed as part of the main VFP installation. No MSDN installation was needed.

Most of the in-line examples throughout the book assume that you have already created the appropriate Automation server and stored a reference to it in a variable. In the first chapter that addresses each of the servers, we show you how to do so and introduce the variable that we use for that server (for example, oWord for Word). After that, we assume the existence of the variable and that it has a valid reference. If you’re working through the examples from the Command Window as you read, you should find this assumption quite comfortable.

For larger examples, we had to make a choice. In real applications, you almost always want to open the Automation server, do what needs to be done, and close the server. In that situation, you can use a local variable to hold the reference to the server. For our purposes, however, we usually want the server to stay open and accessible following the example so that you can examine the results and reference the server from the Command Window. However, we didn’t want to leave multiple instances of the servers running, abandoned and using your system resources. So most of the examples that are included in the Developer Downloads clear any variables that might be references to Automation objects, and then create a public variable to reference the server. We do not recommend using this technique in your applications.

Copyright 2000 by Tamar E. Granor and Della Martin All Rights Reserved