Help  |   Contact Us  |   About Us  |   News and Events
Books, conferences, and other information about... Moving to Linux Switching to OOo Building Linux Apps Using Visual FoxPro
Buy      Download      Your Account      Catalog      Support      Conferences      Resources      Fun

Microsoft Office Automation with Visual FoxPro
Updates/FAQ/Errata

If you have a question about the technical contents of this book, please contact the author(s). Their email address(es) can usually be found in their bios in the About the Author section at the front of the book or on the author's bio page (click on the author's name on the book's main page.


Word


Q. I'm trying to automate Word's mail merge feature, and I'm having trouble getting it to read VFP tables. What should I do?

Word uses the ODBC driver to access most data sources, including VFP tables, and the mail merge stops working if something happens to the ODBC driver (including upgrades, as people noticed when installing Service Pack 3.0 for VFP, which installed ODBC 4.0). This reliance on the ODBC driver makes using the mail merge feature much harder and riskier than it needs to be.

What should you do? Avoid ODBC and user manipulation of the documents, if possible - this eliminates much of the complexity. One way to do that is to create the data source for a mail merge on the fly, sending FoxPro data to Word, and attaching it to the main document just long enough to merge it. This is a suitable solution for small to medium data sets, but may need to be reconsidered for large data sets. However, it doesn't matter what approach you use, the data ends up in Word somehow - this approach may be just as good as any other, with the benefit of affording you more control over the process. This approach is discussed in the topic "Merging documents with data" in Chapter 6, "Advanced Word," on page 134.

An alternative approach is to use the Find and Replace objects, creating a main document with specially delimited text strings that are replaced with the data from your database. The big challenge is to use delimiters that aren't going to show up other places in your document, to ensure that you replace only what should be replaced. This approach works for small to medium data sets, and is discussed in the topic "Merging documents with data" in Chapter 6, "Advanced Word," on page 133.

If you must use ODBC and need to know more about managing ODBC and connections, check out this book's sister volume in the Essentials series, Client-Server Applications with Visual FoxPro 6.0 and SQL Server 7.0.

Q. I'm automating a mail merge using Word. How can I keep it from putting a page break after each record?

Word offers four mail merge types: letter, envelope, label and catalog. Most people are familiar with the first three, which all put the data for one record on a separate "page." (For an envelope or label, it's not a page in the same sense as for a letter, but the key point is that each record is separated from those around it.) The catalog type is different. Each record corresponds to a line or group of lines, but there's no break after the record. As the name suggests, it's designed for things like catalogs where you want to list all the records together. We often use this type to create membership lists.

The mail merge type is controlled by the MainDocumentType property of the MailMerge object. There are actually five possible values for this property:

Constant                    Value
wdNotAMergeDocument   -1
wdFormLetters                0
wdMailingLabels               1
wdEnvelopes                   2
wdCatalog                      3

To create a mail merge catalog, set MainDocumentType to wdCatalog (3) and then perform the merge as usual.

Finally, it's worth noting that what separates records in the other merge types is not a page break, but a section break. A section in a Word document is an entity that can have its own formatting such as headers and footers, number of columns and so forth. It's not clear to us why Word uses section breaks in mail merge, but knowing that it does may save you some time and aggravation down the road.

Q. The mail merge solution we presented in Chapter 6 works fine, but itís too slow to use in many situations. So, we went back to the drawing board.

This new version uses the COPY TO ... TYPE XL5 command to create a worksheet containing the desired data. The worksheet is then attached to the mail merge main document. This version is significantly faster than the original, especially as the number of records increases. While it takes about 5 seconds on our test machine to merge the 77 records in the original example, going up one order of magnitude to 770 records increases the time only to 6.5 second. When we increased by another order of magnitude to 7700 records, the sample took about 15 seconds. (Using the original approach on the same machine, processing 77 records takes about 100 seconds.)

There are some limits imposed by this version. First, memo fields cannot be included in the output. To work around that limit, apply a function like LEFT() or TRIM() to the memo while creating the cursor used for the data source.

Second, COPY TO ... TYPE XL5 can copy only 16,383 records. Any additional records are ignored and no error message is generated. To work around this problem, break the original data into several subsets and use this process on each.

Finally, the user must have Excel on the machine and, in fact, during the process, Excel appears on the taskbar briefly. (We suspect that running Excel is the main time sink when working with fewer records.)

Four files have been updated (see AutomationSC_010309.ZIP) to demonstrate the new approach:

AltCreateHeaderSource.PRG - replaces CreateHeaderSource.PRG. The only change is to omit Memo fields from the HeaderSource.

AltBuildMerge.PRG - replaces BuildMerge.PRG. The only change is to use AltCreateHeaderSource rather than CreateHeaderSource.

Alt2CreateDataSource.PRG - replaces CreateDataSource.PRG. Uses the COPY TO ... TYPE XL5 approach.

Alt2DoMerge.PRG - replaces DoMerge.PRG. The only change is to use Alt2CreateDataSource rather than CreateDataSource.

Outlook


Q. You've shown me how to automate Outlook. Can I automate Outlook Express?

Unfortunately, the answer is "no." Outlook Express is not an Automation Server. You can programmatically access it through MAPI (the Mail Application Programming Interface).

For sample code on how to access MAPI, see the KB article Q146641, located (hopefully) at: http://support.microsoft.com/support/kb/articles/Q146/6/41.asp. Doug Hennig has published a class in his article in FoxTalk (www.pinpub.com) and Nigel Coates has published a class available for download at the Universal Thread (www.universalthread.com).

Q. I'm having problems getting Outlook to behave, for example, when adding a task. It works fine if Outlook was running before I instantiated the object, but doesn't work if it wasn't running. What's up?

When a user opens Outlook, Outlook automatically opens a NameSpace, which is how it knows where to place new items, like tasks. However, if it was opened via Automation, you may need to explicitly open the NameSpace. Be sure that you get into the habit of opening it, as in:

oOutlook = CreateObject("Outlook.Application")
oNameSpace = oOutlook.GetNameSpace("MAPI")

This ensures that the Outlook is opened properly, and knows where to place your task (or whatever you're adding). For more information, see the topic "Accessing Outlook's contents" in Chapter 12, "Automating Outlook," on page 331.

Q. I keep all my Outlook information in personal folders so I can access them offline. The question is, how does one get a handle on personal folders? All the examples I see assume that automation occurs on the primary folders. So how do I get a handle on, say, the "Inbox" folder that's in a local .PST file and that, in Outlook, is a child of a Personal Folders node?

You can climb down from the NameSpace object into the different folder hierarchies. NameSpace has a Folders collection containing one Folder for each set. Each of those folders then contains another Folders collection that contains the ones you're used to working with. You can address the collection using the name of each folder set, so you do something like:

oOutlook = CreateObject("Outlook.Application")
oNS = oOutlook.GetNameSpace("MAPI")
oPersonal = oNS.Folders["Personal Folders"]

It does get tricky if you have multiple folder sets with the same name. In that case, you need to loop through the Folders collection (using FOR EACH) to find folder sets that match, checking the Name property, then look at properties of the folder to determine whether it's the particular folder set with that name that you want.

oOutlook = CreateObject("Outlook.Application")
oNS = oOutlook.GetNameSpace("MAPI")
FOR EACH oFolder IN oNS.Folders
   IF oFolder.Name = "Personal Folders"
   * Now look at other properties to decide if this is the right folder set
     IF
     * get out of the loop
     EXIT
     ENDIF
   ENDIF
ENDFOR

General Questions


Q. I want keep to my files in a general field. How should I do it?

Don't. Putting data into general fields is a one-way operation. Although it's very easy to move data from files into general fields using the APPEND GENERAL command, there's no corresponding COPY GENERAL command to retrieve the data and put it back into files. Rather than using general fields, it's usually a better choice to store the name of the files in a table and then access the files as needed. Another alternative is to copy the files into binary memo fields (that is, memo fields with NOCPTRANS set) and then copy them back to files when you want to use them in automation.

Q. How do I find a list of all of those constants and their values for each of the Office servers?

We've listed a variety of ways in our book, in Chapter 2, "The Office servers." Here's a summary:

  • Don't bother with Help, unless you're working with Outlook. In that case, see the topic "Microsoft Outlook Constants" in the Outlook's VBA Help file (VBAOutl9.CHM).
  • Try the Object Brower. To find the server's constants, open the server (interactively, not with CreateObject()) and select Tools|Macros|Visual Basic Editor (or press Alt-F11). When the VBE comes up, select Tools|Object Browser (or press F2). The Object Browser is displayed, and you can enter the constant you wish to query (or just enter the first part, which returns all the items starting with that string). For more on the Object Browser, see the topic "Take me for a browse" in Chapter 2, "The Office servers," page 18.
  • Rick Strahl of West Wind Technologies has created a freeware tool that reads a COM type library, extracts the constants, and creates a Visual FoxPro header file. The tool, called GetConstants, is included in the Developer Download files available at www.hentzenwerke.com and can also be downloaded from Rick's site (www.west-wind.com). The Developer Download files also include header files for each of the Office applications. GetConstants prompts you for a type library, which is an OLB file in the same directory as the executable, such as C:\Program Files\Microsoft Office\Office\Excel9.olb (the default installation for Excel 2000).
  • Some VFP sites, like the Universal Thread (www.universalthread.com), have header files available for download.

Chapter 4 (Page 57)

In the SetUserFont program (listing 1), in the block of code that begins with the comment "* Apply them to the font object", replace the lines that apply to bold and italic formatting with the following: IF "B"$aFontInfo[3]
   .Bold = .T. && .T. works here
ELSE
   .Bold = .F.
ENDIF
IF "I"$aFontInfo[3]
   .Italic = .T.
ELSE
   .Italic = .F.
ENDIF

Chapter 5 (Page 91)

In the OrderTblFormat program (Listing 1), the RAND() function is used to select a random record number. This logic has two small problems. The first is that RAND() never produces truly random number; it produces the same series of numbers based on the seed value. Without parameters, RAND() uses a seed of 100,001. Given a negative parameter, RAND(-1) selects a seed value based on the system clock. RAND(-1) gives a better series of random numbers. In this exercise, it's of little importance - you should see different records each time (except in circumstances where the random numbers return close values, which then access the same record). But if you extrapolate this concept to other parts of your application, it could indeed affect the randomness of your numbers. The second, more pressing problem is that of the range of numbers returned by the RAND() function. In VFP 5.0 and later, RAND() returns a value greater than or equal to zero, but less than one. To ensure that there's no attempt to call a record number of 0, it's necessary to replace the GO RAND()*RECCOUNT() line with a line that ensures 0 is never called. Use this line instead, which also uses the negative seed in RAND():

GO INT(RECCOUNT() * RAND(-1)) + 1

In versions prior to VFP 5.0, both 0 and 1 are valid return values. To ensure that record numbers 0 or RECCOUNT() + 1 are called, use the following:

GO MIN( INT(RECCOUNT() * RAND(-1)) + 1, RECCOUNT() )

For selecting a random record in this kind of example exercise, you're not likely to care how random the random records appear (after all, it's not a big database). Calling record 0 or RECCOUNT() + 1 could happen, though you may have to run it a number of times before 0 crops up as a random number (and if it did, it would error, and rerunning it to find the problem gives another number that is likely not to be zero). While you're not likely to write Automation code to run from a random record (we hope!), we thought we'd amend our example to include better programming practices that might be used in other parts of your application.

Chapter 8 (Page 207)

I In the code at the bottom of the page, change the line that reads:

oMacroModule.Name = "MacroSave"

to read:

oMacroModule.Name = "QuietSave"

Chapter 12 (Page 340)

In the MakeContact program (listing 2), the RAND() function is used to select a random record. As stated in the errata for Chapter 5, page 91, the code may select record 0 (or RECCOUNT()+1, if using a version prior to VFP 5.0), which produces an error. And the numbers aren't as random as they could be. Replace the GO RAND()*RECCOUNT() line with this line (VFP 5.0 and later):

GO INT(RECCOUNT() * RAND(-1)) + 1

Or this line, for VFP 3.0 and before:

GO MIN( INT(RECCOUNT() * RAND(-1)) + 1, RECCOUNT() )

See the errata for Chapter 5, page 91 for more information on RAND().

Chapter 15 (Page 387)

In the TellUser method, compare the return value of SYS(2335) to "1" rather than 1.

Chapter 15 (Page 399)

In CusWord.IsServerOpen, change the RETURN line to read:

RETURN VarType(This.oServer)="O" AND TYPE("This.oServer.Name")="C"

Chapter 15 (Page 400)

In cusPowerPoint.IsDocOpen, change the ">" to ">="

Chapter 15 (Page 407)

In Listing 2, move the call to the SaveDocument method inside the IF .IsDocOpen() test.