Skip to main content.

Web Based Programming Tutorials

Homepage | Forum - Join the forum to discuss anything related to programming! | Programming Resources

Special Edition Using Visual FoxPro 6

Special Edition Using Visual FoxPro 6 -- Ch 21 - Visual FoxPro as a COM Client

Special Edition Using Visual FoxPro 6


Chapter 21

Visual FoxPro as a COM Client




Application Interoperability and the Microsoft Strategy



In this chapter, you'll learn some practical examples of enhancing applications using COM servers, focusing primarily on Microsoft Office.

Whether you love it or hate it, you have to give Microsoft credit. It has set a vision and it goes for it full tilt. In this case, Microsoft has had a vision of developing applications using components (hence the term Component Object Model) and it bought into it bigtime. Microsoft Office has become a model for creating applications designed not only for end-user use but also for use by developers.

Office presents a huge array of functionality. From the WinWord document to the Excel spreadsheet to the Outlook cornucopia of functionality, Office offers developers a huge suite of ready-to-use functionality at no extra cost.

The one downside to using Microsoft Office applications as COM servers lies in learning their object models. Object model, in this context, refers to the objects that these applications expose as COM servers, what their contents are (object members, properties, and methods), and when to use what.

This chapter is not designed to teach you how to use applications such as Word and Excel as COM servers. Rather, it will show you some strategies for continuing the learning process and show you examples that you can use in your applications immediately.

Learning How to Use Word and Excel

If you're using something like Word or Excel as a COM server, it can be a little hairy to learn what the application does and how it's done. If you need to get something done in Word or Excel, and you are unsure how to do it, there is a simple trick that many people use to get themselves over the hump.

Basically, perform the operation you need to accomplish (such as printing a document) in the application (as any normal user would do it) while recording a macro for it. Then look at the generated macro. For the most part, you will be able to copy and paste that macro into Visual FoxPro with few modifications.

For example, suppose that you need to generate a report in Excel. When generating a report, you need to enter information, format cell contents, size columns, and so on. Here's a simple way to learn how to do this without a huge learning curve.

Start Excel and turn on the macro recorder by selecting Tools, Macro, Record New Macro. The dialog that pops up enables you to name the macro (see Figure 21.1).

Figure 21.1 : The Excel Record Macro dialog appears when you initiate a new macro.

While you're recording the macro, the Macro Recording toolbar is visible (see Figure 21.2). Manually perform the operations that you want to automate, such as entering information, formatting the cells and columns, and so on. When you are done, and have accomplished at least one instance of every operation, stop the recorder.

Figure 21.2 : The Macro Recording toolbar is visible while you record a macro in Excel.

The next step is simple: Press Alt+F8 and you will see a list of all macros in the document, including the one you just created. Highlight the name of the macro you just created and click Edit. The code generated by the macro recorder appears in the Visual Basic Editor (see Figure 21.3). If you look at the code, you will see that it is remarkably similar to the code you would write in Visual FoxPro. In fact, it is so similar, you will not have to do much in the way of changes to get it to work in Visual FoxPro.

Figure 21.3 : The Visual Basic Editor shows the macro code generated by the Record Macro operation in Excel.

There is one problem, though. When Word and Excel record macros, the generated code uses a spate of defined constants without providing definitions for those constants. Fortunately, the constants have not been hidden from you. All constants used by these applications are documented in the application's type library. Some languages, such as Visual Basic, do not need anything other than the type library to understand these constants. Visual FoxPro cannot use the type library directly. Fear not, however, if you have the full Visual Studio, you can create an .H file for Visual FoxPro with a minimum of bother that will take care of the situation for you.

In the next section, I'll show the trick. For now, accept the fact that I have a header file called Excel.h that has all the constants in it.

The code generated by the macro recorder is shown in Listing 21.1.


Listing 21.1  21CODE01-Macro Code That Was Recorded in Excel
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 8/23/98 by Menachem Bazian, CPA
'
    ActiveCell.FormulaR1C1 = "Account Number"
    Range("B4").Select
    ActiveCell.FormulaR1C1 = "Description"
    Range("C4").Select
    ActiveCell.FormulaR1C1 = "Balance"
    Range("A5").Select
    ActiveCell.FormulaR1C1 = "'100"
    Range("A6").Select
    ActiveCell.FormulaR1C1 = "'200"
    Range("A7").Select
    ActiveCell.FormulaR1C1 = "'300"
    Range("B5").Select
    ActiveCell.FormulaR1C1 = "This is the account description"
    Range("B6").Select
    Columns("B:B").EntireColumn.AutoFit
    ActiveCell.FormulaR1C1 = "This is another account description."
    Range("B7").Select
    ActiveCell.FormulaR1C1 = "This is a third account description."
    Range("C5").Select
    ActiveCell.FormulaR1C1 = "100"
    Range("C6").Select
    ActiveCell.FormulaR1C1 = "200"
    Range("C7").Select
    ActiveCell.FormulaR1C1 = "300"
    Range("C8").Select
    Columns("B:B").EntireColumn.AutoFit
    Range("C5").Select
    ActiveCell.FormulaR1C1 = "1245.99"
    Range("C6").Select
    ActiveCell.FormulaR1C1 = "14290.26"
    Range("C7").Select
    ActiveCell.FormulaR1C1 = "-500.98"
    Range("B9").Select
    ActiveCell.FormulaR1C1 = "TOTAL"
    Range("C9").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"
    Columns("C:C").Select
    Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
    Columns("A:A").EntireColumn.AutoFit
    Rows("4:4").Select
    Selection.Font.Bold = True
    Range("B9:C9").Select
    Selection.Font.Bold = True
    Range("C7").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Range("C9").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlDouble
        .Weight = xlThick
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Range("A4").Select
    Columns("A:A").EntireColumn.AutoFit
End Sub

The trick to getting this code to work in Visual FoxPro is simple. First, you have to include the header file. Also, you must define two constants for .T. and .F. (I like to define them separately; I'll explain why in the next section.)

The next step is to work on the minor differences in syntax. Mainly, you need to add periods before each collection. For example,

With Selection.Borders(xlEdgeBottom)

becomes

With .Selection.Borders(xlEdgeBottom)

Another obvious difference is the End With command, which is two words in Visual Basic for Applications and one word in Visual FoxPro.

When you're done, the final code should appear as shown in Listing 21.2.


Listing 21.2  21CODE02-An Excel Macro Converted to Run in Visual FoxPro 6
* Program....: EXCEL1.PRG
* Version....: 1.0
* Author.....: Menachem Bazian, CPA
* Date.......: August 23, 1998
* Project....: Using Visual FoxPro 6 Special Edition
* Notice.....: Copyright (c) 1998 Menachem Bazian, CPA, All Rights Reserved.
* Compiler...: Visual FoxPro 06.00.8141.00 for Windows
* Abstract...:
* Changes....:

#INCLUDE excel.h
#DEFINE True .T.
#DEFINE False .F.

WITH oExcel

     .WorkBooks.Add
     .Sheets(1).Select
     .Range("A4").Select
     .ActiveCell.FormulaR1C1 = "Account Number"
     .Range("B4").Select
     .ActiveCell.FormulaR1C1 = "Description"
     .Range("C4").Select
     .ActiveCell.FormulaR1C1 = "Balance"
     .Range("A5").Select
     .ActiveCell.FormulaR1C1 = "'100"
     .Range("A6").Select
     .ActiveCell.FormulaR1C1 = "'200"
     .Range("A7").Select
     .ActiveCell.FormulaR1C1 = "'300"
     .Range("B5").Select
     .ActiveCell.FormulaR1C1 = "This is the account description"
     .Range("B6").Select
     .Columns("B:B").EntireColumn.AutoFit
     .ActiveCell.FormulaR1C1 = "This is another account description."
     .Range("B7").Select
     .ActiveCell.FormulaR1C1 = "This is a third account description."
     .Range("C5").Select
     .ActiveCell.FormulaR1C1 = "100"
     .Range("C6").Select
     .ActiveCell.FormulaR1C1 = "200"
     .Range("C7").Select
     .ActiveCell.FormulaR1C1 = "300"
     .Range("C8").Select
     .Columns("B:B").EntireColumn.AutoFit
     .Range("C5").Select
     .ActiveCell.FormulaR1C1 = "1245.99"
     .Range("C6").Select
     .ActiveCell.FormulaR1C1 = "14290.26"
     .Range("C7").Select
     .ActiveCell.FormulaR1C1 = "-500.98"
     .Range("B9").Select
     .ActiveCell.FormulaR1C1 = "TOTAL"
     .Range("C9").Select
     .ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"
     .Columns("C:C").Select
     .Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
     .Columns("A:A").EntireColumn.AutoFit
     .Rows("4:4").Select
     .Selection.Font.Bold = True
     .Range("B9:C9").Select
     .Selection.Font.Bold = True
     .Range("C7").Select
     .Selection.Borders(xlDiagonalDown).LineStyle = xlNone
     .Selection.Borders(xlDiagonalUp).LineStyle = xlNone
     .Selection.Borders(xlEdgeLeft).LineStyle = xlNone
     .Selection.Borders(xlEdgeTop).LineStyle = xlNone
     WITH .Selection.Borders(xlEdgeBottom)
          .LineStyle = xlContinuous
          .Weight = xlThin
          .ColorIndex = xlAutomatic
     ENDWITH
     .Selection.Borders(xlEdgeRight).LineStyle = xlNone
     .Range("C9").Select
     .Selection.Borders(xlDiagonalDown).LineStyle = xlNone
     .Selection.Borders(xlDiagonalUp).LineStyle = xlNone
     .Selection.Borders(xlEdgeLeft).LineStyle = xlNone
     .Selection.Borders(xlEdgeTop).LineStyle = xlNone
     WITH .Selection.Borders(xlEdgeBottom)
          .LineStyle = xlDouble
          .Weight = xlThick
          .ColorIndex = xlAutomatic
     ENDWITH
     .Selection.Borders(xlEdgeRight).LineStyle = xlNone
     .Range("A4").Select
     .Columns("A:A").EntireColumn.AutoFit

     .Visible = .T.
ENDWITH

If you look closely at the code, the only modifications are:

To quantify the entire process, generating the macro and changing the code took a few short minutes. After I have the generated code, I can examine it to determine how certain things were done and then apply these methods to the entire report.

You'll see an example of a report I did in this manner in a little bit. Before I go into that, though, there is one open issue, which is addressed in the following section.

Dealing with Generated Constants in Macro Code

As said earlier, the constants in the generated code are not understandable to Visual FoxPro with a header file. The question is, how can I get that header file?

Before I go any further, let me state that the issue is one that can affect much more than Microsoft Office applications. Any application that you learn in this manner can have similar issues. So, the trick of generating an .H file for a COM server might prove useful beyond the Office suite.

The trick works like this: First you have to export the type library to a text file. You do this with Visual Studio's Object Viewer. After opening the Object Viewer (see Figure 21.4), use it to open the type library by choosing File, View TypeLib. Object Viewer requests the name of the file to open; for Excel, the file is named Excel8.olb and can be found in the ...Microsoft Office\Office\ folder.

Figure 21.4 : The Visual Studio Object Viewer is where you'll export the type library to a text file.

After you select the file to view, the ITypeLib Viewer displays the type library (see Figure 21.5).

Figure 21.5 : This viewer displays a type library.

You see a text representation of the type library in the right pane of the viewer. Place your cursor in the viewer and select all the text with Ctrl+A and then press Ctrl+C to copy the selected text to the Clipboard. Open a text file using Visual FoxPro or Notepad and paste the text there. Save it. After you do this, run the program MakeHFile to parse it out and make a header file.

The code for MakeHFile is presented in Listing 21.3.


Listing 21.3  21CODE03-Program That Creates a Header File from Type Library Constants
*-- Program....: MAKEHFILE.PRG
*-- Version....: 1.0
*-- Author.....: Menachem Bazian, CPA
*-- Date.......: August 23, 1998
*-- Project....: Using Visual FoxPro 6 Special Edition
*-- Notice.....: Copyright (c) 1998 Menachem Bazian, CPA, All Rights Reserved.
*-- Compiler...: Visual FoxPro 06.00.8141.00 for Windows
*-- Abstract...:
*-- Changes....:

*-- Creates a header file for the constants in a type library.
*-- The type library contents have been
*-- exported with the OLE viewer prior to entering this program.
*--
*-- Parameters:
*--
*-- tcInFile - The name of the file to parse out.
*-- tcOutFile - The name of the .h file to create.
*--
*-- Note: Only the first parameter is required. If tcOutFile is not
*-- provided, the name of the input file is used as the name of
*-- the .h file. For example, Excel8.prh would output Excel8.h

LPARAMETERS tcInFile, tcOutFile

LOCAL lnInFile, lnOutFile, lcText, lcOutText, llOutput

*-- If the name of the file was not provided or it doesn't exist,
*-- error out.

IF PCOUNT() = 0 OR !FILE(tcInFile)
     MESSAGEBOX("Input file must be specified!", 16, "MakeHFile")
     RETURN
ENDIF

*-- If only the input file was provided, generate a .h file name

IF PCOUNT() = 1
     lnDotPos = RAT('.', tcInFile)
     tcOutFile = LEFT(tcInFile, lnDotPos - 1) + ".h"
ENDIF

CLOSE ALL
lnInFile = FOPEN(tcInFile)
lnOutFile = FCREATE(tcOutFile)

lcText = ""
lcOutText = ""
llOutPut = .F.

DO WHILE !fEof(lnInFile)
     lcText = FGETS(lnInFile)

     *-- At the close curly braces, the constants are done.

     IF "}" $ lcText
          llOutput = .F.
     ENDIF
  
     IF llOutPut
          lcOutText = ALLTRIM(lcText)
          lcOutText = STRTRAN(lcOuttext, '=', ' ')
          lcOutText = "#DEFINE " + ALLTRIM(lcOutText)
       
          IF RIGHT(lcOutText, 1) = ','
               lcOutText = SUBST(lcOutText, 1, LEN(lcOutText) - 1)
          ENDIF
  
          =FPUTS(lnOutFile, ALLTRIM(lcOutText))
     ENDIF

     *-- Look for Typedef Enum { in the file. That's the beginning
     *-- of the constants we need to export to the .h File
  
     IF "ENUM {" $ UPPER(lcText)
          llOutput = .T.
     ENDIF
  
ENDDO

CLOSE ALL

MakeHFile is called with one or two parameters. The first parameter, which is required, is the name of the file to parse. As a naming convention, I give the exported type libraries an extension of .PRH (for pre-h file), but you can name them whatever you like. The second parameter, which is optional, gives a name to the output file (the header file to be generated). If the second parameter is not provided, the name of the input file is used and a new extension, .H, is added.

The only thing that MakeHFile does not do is deal with the representation of True and False. Visual Basic represents the value "true" as True, and Visual FoxPro uses .T.. The same goes for False and .F.. As a general rule, you can create a second header file with general definitions like this (such as FoxPro.h) and include it there. In the example just shown, I specifically defined the constants in the code.

More on Excel

It would be very useful to take a quick look at the code generated by the macro recorded in Excel. Excel has a hierarchical object model. In this case, you start with a workbook, which is a collection of sheets. Sheets are a collection of cells. You can reference workbooks and sheets with an array. For example, Sheets(1) is the first sheet in the display order (that is, if you look at the tabs of the sheets in the workbook, it is the left-most tab).

You can add workbooks and sheets with the .ADD method each collection has. You can set properties of the sheets and workbooks. For example, to change the name of the sheet, just change its name property.

The good news about learning to use Excel, as well as Word and Outlook, is that syntax is rather consistent. Both the Workbooks and Sheets collections use the .ADD method to add a member to their collection. In Outlook, you add an appointment to the calendar collection with-you guessed it-.ADD.

The bad news about all this is that it can get a little hairy trying to work all this stuff out. The Help documentation that comes with Excel is fairly good, but it takes time to get used to working with the collections. Word is the same. However, I find it very useful to browse the objects I am using in the Visual Basic Object Viewer (see Figure 21.6).

Figure 21.6 : The Visual Basic Object Browser provides an organized view.

Notice that Excel starts with the application class. So, if I want to see what the members of the application class are, I scroll to Application in the list on the left and I can see all of its members, methods, and properties.

Using the Object Browser in conjunction with the generated code and the help files gives you a good way to learn how to use Excel and Word as COM servers.

Generating a Report with Excel

At this point you have seen some generated code, taken a brief look at the Excel object model, and have solved the problem of the constants. It's time to put your newfound skills to use and generate a report.

Excel is perfect for columnar reports. It especially shines when the report is one that the user might want to do ad-hoc analysis on (I did this for a client once and it saved them many hours of retyping information).

For the example here, I created a simple report based on the Customer table in the testdata sample database that ships with Visual FoxPro. The report lists the customer name and their total sales. The cells are formatted and the report is sorted by total sales (descending).

Take a look at the code for CustRpt.PRG as shown in Listing 21.4. Notice the comments in CustRpt.PRG, as they document the thoughts behind why certain things are done in a certain way.


Listing 21.4  21CODE04-Listing of Program That Dumps Customer Table Data into a Formatted Excel Report
*-- Program....: CUSTRPT.PRG
*-- Version....: 1.0
*-- Author.....: Menachem Bazian, CPA
*-- Date.......: August 23, 1998
*-- Project....: Using Visual FoxPro 6 Special Edition
*-- Notice.....: Copyright (c) 1998 Menachem Bazian, CPA, All Rights Reserved.
*-- Compiler...: Visual FoxPro 06.00.8141.00 for Windows
*-- Abstract...:
*-- Changes....:

*-- This program basically dumps all customers and their maximum order
*-- amount into a formatted Excel report.

*-- Include all the Excel stuff.

#INCLUDE Excel.h
#DEFINE False .F.
#DEFINE True .T.

*-- Default font and size I want. I #DEFINE it to make it
*-- easier to change later on.

#DEFINE RPT_FONTNAME "Comic Sans MS"
#DEFINE RPT_FONTSIZE 12

*-- First, open the database and table and run the report

CLOSE DATA ALL

*-- Note, this program uses the path from my development machine.
*-- yours may differ.

WAIT WINDOW NOWAIT "Selecting rows from Customer table..."

OPEN DATA ("D:\PROGRAM FILES\DEVSTUDIO\VFP\samples\data\testdata")
USE customer

SELECT * ;
     FROM customer ;
     ORDER BY MaxOrdAmt DESCENDING ;
     INTO CURSOR Output

*-- Now, get the instance for Excel.

LOCAL loExcel, lcOldError, lcRange, lnSheets, lnCounter

WAIT WINDOW NOWAIT "Starting Excel..."

lcOldError = ON("ERROR")
ON ERROR loExcel = .NULL.
loExcel = GetObject(, "Excel.Application")
ON ERROR &lcOldError

IF ISNULL(loExcel)
     loExcel = CreateObject( "Excel.Application" )
ENDIF

*-- At this point, I have an instance to Excel.
*-- I am *assuming* the existence of Excel. Not
*-- necessarily a valid assumption, but acceptable for
*-- the purposes of this sample program.

WITH loExcel

     *-- When you start Excel as a COM server, you have no workbooks.
     *-- So, I need to add one.

     .Workbooks.Add

     *-- One critical thing to do is make sure that the COM server
     *-- doesn't put up a dialog. DisplayAlerts is a property
     *-- roughly equivalent to Visual FoxPro's SET SAFETY. By setting
     *-- it to False, attempting to close the workbook without
     *-- saving it will not generate an error.

     .DisplayAlerts = False

     *-- By default, creating a workbook starts it with several
     *-- sheets. I want to be neat, so I am deleting all but the
     *-- one sheet I need.

     lnSheets = .Sheets.Count
     FOR lnCounter = 1 TO lnSheets - 1
          .Sheets(1).Delete
     ENDFOR

     *-- Next step is to rename the sheet. Again, for neatness sake.
     *-- The SELECT is probably not necessary but I like to be a bit
     *-- paranoid with this just in case.

     WITH .Sheets(1)
          .Select
          .Name = "Testdata Customers"
     ENDWITH

     *-- And, get rid of the grid lines... I don't like them in a
     *-- formatted report

     .ActiveWindow.DisplayGridlines = False

     *-- OK, now that we have the housekeeping stuff done, we can
     *-- get down to business. First step is to build the header of
     *-- the report. That includes the title of the report and
     *-- and the date/time of the report.
     *--
     *-- Note that there are two ways to reference a cell in Excel.
     *-- The Cells collection can be used in which case you specify
     *-- the row and column of the cell as numbers. Cells(1,1) refers
     *-- to A1. Cells(2,1) is A2.
     *--
     *-- The Range() collection does the same thing except I can reference
     *-- a cell by its "English" name.
     *--
     *-- Of the Two, I prefer the Range() method when I am going for a
     *-- particular cell because I think of the cells that way. Later on,
     *-- I use the Cells() collection in the loop because the numeric
     *-- parameters are perfect for that kind of cell populating exercise.
     *--
     *-- There is one other benefit to the Range() collection. You can
     *-- work on a range of cells at the same time. For example,
     *-- I can format a whole range of cells in one operation.
     *-- I have an example of this later on in the program.

     WAIT WINDOW NOWAIT "Building Header Rows"

     WITH .Range("A1")
          .Value = "Testdata Customer Report"
          WITH .Font
               .Bold = .T.
               .Size = 14
               .Underline = xlUnderlineStyleSingle
          ENDWITH
     ENDWITH
  
     *-- Center A1 over columns A and B
    With .Range("A1:B1")
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .ShrinkToFit = False
        .MergeCells = False
        .Merge
    EndWith

     WITH .Range("A3")
          .Value = "=Now()"
          .NumberFormat = "m/d/yy h:mm AM/PM"
          .HorizontalAlignment = xlLeft
     ENDWITH

     *-- Now do the column headers
     *--
     *-- Just for fun, we'll color in the column headers.

     .Range("A5").Value = "Customer Name"
     .Range("B5").Value = "Maximum Order Amt"

     lcRange = "A5:B5"

     WITH .Range(lcRange)
          .Font.Bold = .T.
          .Font.Size = RPT_FONTSIZE
          .Font.Name = RPT_FONTNAME
          .HorizontalAlignment = xlCenter

          WITH .Borders(xlEdgeBottom)
               .Weight = xlMedium
               .LineStyle = xlContinuous
          ENDWITH

          WITH .Interior
               .ColorIndex = 42
               .Pattern = xlSolid
          ENDWITH
     ENDWITH

     *-- Now, scan through the XTAB table and put all
     *-- the information in the spreadsheet

     WAIT WINDOW NOWAIT "Populating cells:"

     SELECT Output
     GO TOP

     *-- Populate the report
     *--
     *-- Note the use of Cells() in this case instead of Range().

     lnRow = 7
     SCAN
          WAIT WINDOW NOWAIT "Populating cells: Record " + ALLTRIM(STR(RECNO()))
               + ;
               " of " + ALLTRIM(STR(RECCOUNT()))
          *-- Read the record into the cells
          .Cells(lnRow, 1).Value = output.Company
          .Cells(lnRow, 2).Value = output.MaxOrdAmt
          lnRow = lnRow + 1
     ENDSCAN

     *-- OK, the body of the report is complete. Now, let's get the totals
     *-- in there.

     .Cells(lnRow + 2, 1).Value = "Totals"
     WITH .Cells(lnRow + 2, 2)
          .Value = "=SUM(B7:B" + ALLT(STR(lnRow-1)) + ")"
          WITH .Borders(xlEdgeBottom)
               .Weight = xlMedium
               .LineStyle = xlDouble
          ENDWITH
     ENDWITH

     *-- Format the body of the report.

     lcRange = "A7:B" + ALLTRIM(STR(lnRow+2))
     WITH .Range(lcRange)
          .Font.Size = RPT_FONTSIZE
          .Font.Name = RPT_FONTNAME
     ENDWITH

     WITH .Range(ALLT(STR(lnRow+2)) + ":" + ALLT(STR(lnRow+2)))
          .Font.Bold = .T.
     ENDWITH

     WITH .Range("B7:B" + ALLT(STR(lnRow+2)))
          .NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
          .Font.Name = RPT_FONTNAME
          .Font.Size = RPT_FONTSIZE
     ENDWITH

     *-- Now some column formatting here. Note the use of the Columns()
     *-- Collection.

     *-- Here's another example of how wierd Excel can be. In order to
     *-- have a column automatically size itself properly, you can:
     .Columns("A:B").EntireColumn.AutoFit

     *-- And that's it

ENDWITH

WAIT CLEAR

=MessageBox("Done")

*-- Setting the visible property to .T. will cause Excel to become
*-- visible and become the foremost application.

loExcel.Visible = .T.
Release loExcel
CLOSE DATA ALL

RETURN

Managing Instances of a COM Application

Notice the code at the top of CustRpt.PRG where Excel is started. The whole reason for the rigmarole with first trying GetObject() and then CreateObject() (if GetObject() fails) is to prevent Excel from running multiple times on my machine.

In truth, you will probably want to try doing this with other servers as well. Therefore, it behooves us to abstract that code into a generic routine. GetCOMInstance.PRG is a program that does just that and is shown in Listing 21.5. Review the code and, once again, pay attention to the comments as they tell the story on how to use this handy little utility.


Listing 21.5  21CODE05-Listing of UDF That Attempts to Retrieve an Instance of a COM Server
*-- Program....: GETCOMINSTANCE.PRG
*-- Version....: 1.0
*-- Author.....: Menachem Bazian, CPA
*-- Date.......: August 23, 1998
*-- Project....: Using Visual FoxPro 6 Special Edition
*-- Notice.....: Copyright (c) 1998 Menachem Bazian, CPA, All Rights Reserved.
*-- Compiler...: Visual FoxPro 06.00.8141.00 for Windows
*-- Abstract...:
*-- Changes....:

*-- This UDF will try to get an instance of a COM server without
*-- running the application again.

LPARAMETERS tcCOMServerClass, tcDocumentFileName

*-- The parameter tcCOMServerClass is the name of the COM server class to
*-- instantiate. For example, "excel.application". This program
*-- specifically sets OLEOBJECT on (otherwise, why would we
*-- be calling this program?"
*--
*-- If the COM server cannot be instantiated, this program returns NULL.

*-- Step 1 - Make sure we got a parameter.

ASSERT TYPE("tcCOMServerClass") = "C" ;
     MESSAGE "You must provide a character parameter to GetCOMInstance."

IF PCOUNT() = 0 OR TYPE("tcCOMServerClass") # "C"
     RETURN .NULL.
ENDIF

*-- If we get this far, we can go ahead and attempt to instantiate the
*-- COM server with GetObject(). Trying to instantiate with GETObject
*-- will try to get an instance from an existing instance of the
*-- application.
*--
*-- Note also that this program will instantiate with GetObject on a file
*-- name. tcDocumentFileName has to be passed and the file has to exist.

LOCAL loCOMInstance, lcOldError

lcOldError = ON("ERROR")


*-- See if the document file name has been passed through. If so, try
*-- to create a reference to that file. If not, just create a regular
*-- instance.
*--
*-- Note that only the GetObject and Createobject lines of code are
*-- wrapped in the changes to ON ERROR. That's because the special
*-- error handler is only applicable to those lines of code.

IF PCOUNT() = 2 AND ;
     TYPE("tcDocumentFileName") = "C" AND ;
     FILE(tcDocumentFileName)
  
     ON ERROR loCOMInstance = .NULL.
     loCOMInstance = GetObject(tcDocumentFileName, tcCOMServerClass)
     ON ERROR &lcOldError
ELSE
     *-- In this case, no documentfile name has been passed through.
     *-- Just create a standard COM instance

     ON ERROR loCOMInstance = .NULL.
     loCOMInstance = GetObject(, tcCOMServerClass)
     ON ERROR &lcOldError
  
     IF ISNULL(loCOMInstance)
          ON ERROR loCOMInstance = .NULL.
          loCOMInstance = CreateObject(tcCOMServerClass)
          ON ERROR &lcOldError
     ENDIF
ENDIF

RETURN loCOMInstance

Using Word

So much for Excel. You could write an entire book on Excel but time and space are limited here, so I will leave the rest up to you. Let's move on to an example of Word. You'll notice some marked differences here, but it should be similar enough that you can use the knowledge you have acquired in Excel to help you here.

By the way, I am a big fan of learning by example. In other words, I think the best way to learn how to work with a COM object is to make a task for yourself and work it through. By the time you have a task figured out, you will be well on your way to learning how to make the most out of the object.

In keeping with this philosophy, let's figure out something you can do with Word, dive right into it, and see where it leads you.

A Hypothetical Problem

The law firm of Boyd, Dewey, Cheatem, and Howe has called you in as a Visual FoxPro and COM expert. They have an accounting system that was written in Visual FoxPro and they want you to write a report. Not any ordinary report, of course. BDCH wants to run a report of all the clients that owe them money and then have the system automatically generate letters to go to the customers. They provide you with a sample Word document named Boyd.doc and leave you to your own devices (see Figure 21.7).

Figure 21.7 : The Boyd.doc document displayed in Microsoft Word.

My Strategy

Not being too well informed about how to automatically generate these letters in Word, but having read Using Visual FoxPro 6 Special Edition, I have a perfect plan. I will type in the sample letter as a macro and see what is generated.

The macro that is generated is shown in Listing 21.6.


Listing 21.6  21CODE06-Macro That Was Recorded While Typing Document Boyd.doc into Word
Sub Macro3()
'
' Macro3 Macro
' Macro recorded 08/23/98 by Menachem Bazian, CPA
'
    Documents.Add Template:="Normal", NewTemplate:=False
    Windows.Arrange
    Selection.TypeText Text:="Boyd, Dewey, Cheatem, and Howe"
    Selection.HomeKey Unit:=wdLine, Extend:=wdExtend
    Selection.Font.Bold = wdToggle
    Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter
    Selection.EndKey Unit:=wdLine
    Selection.TypeParagraph
    Selection.TypeText Text:="Attorneys at Out Law"
    Selection.TypeParagraph
    Selection.TypeText Text:="111 Sewer Avenue"
    Selection.TypeParagraph
    Selection.TypeText Text:="Ratville City, NJ 0700"
    Selection.TypeParagraph
    Selection.Font.Bold = wdToggle
    Selection.ParagraphFormat.Alignment = wdAlignParagraphLeft
    Selection.TypeParagraph
    Selection.InsertDateTime DateTimeFormat:="MMMM d, yyyy", InsertAsField:= _
        False
    Windows("Boyd.doc").Activate
    ActiveWindow.ActivePane.SmallScroll Down:=7
    Windows("Document7").Activate
    Selection.MoveDown Unit:=wdLine, Count:=1
    Selection.TypeParagraph
    Selection.TypeParagraph
    Selection.TypeText Text:="Joe PayShlepper"
    Selection.TypeParagraph
    Selection.TypeText Text:="521 DeadBroke Avenue"
    Selection.TypeParagraph
    Selection.TypeText Text:="Ratville City, NJ 07001"
    Selection.TypeParagraph
    Selection.TypeParagraph
    Selection.TypeText Text:="Dear Joe,"
    Selection.TypeParagraph
    Selection.TypeParagraph
    Selection.TypeText Text:= _
        "Our records show that you owe us a lot of money. Here's the "
    Selection.TypeText Text:= _
        "breakdown you deadbeat. Pay up or we will have our friendj G the "
    Selection.TypeText Text:="legbreaker"
    Selection.MoveLeft Unit:=wdCharacter, Count:=15
    Selection.MoveLeft Unit:=wdCharacter, Count:=3, Extend:=wdExtend
    Selection.Delete Unit:=wdCharacter, Count:=3
    Selection.EndKey Unit:=wdLine
    Selection.TypeText Text:=" visit you and break your kneecaps."
    Selection.TypeParagraph
    Selection.TypeParagraph
    Windows("Boyd.doc").Activate
    ActiveWindow.ActivePane.SmallScroll Down:=8
    Windows("Document7").Activate
    Selection.TypeText Text:="Get the idea?"
    Selection.TypeParagraph
    Selection.TypeParagraph
    Selection.TypeText Text:="Very sincerely yours,"
    Selection.TypeParagraph
    Selection.TypeParagraph
    Selection.TypeText Text:="Boyd, Dewey, Cheatem, and Howe"
    Selection.TypeParagraph
    Selection.TypeParagraph
    ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=2, NumColumns:= _
        3
    Selection.TypeText Text:="Invoice #"
    Selection.MoveRight Unit:=wdCell
    Selection.TypeText Text:="Invoice Date"
    Selection.MoveRight Unit:=wdCell
    Selection.TypeText Text:="Amount Due"
    Selection.MoveRight Unit:=wdCell
    Windows("Boyd.doc").Activate
    ActiveWindow.ActivePane.SmallScroll Down:=6
    Windows("Document7").Activate
    Selection.TypeText Text:="100"
    Selection.MoveRight Unit:=wdCell
    Selection.TypeText Text:="1/1/98"
    Selection.MoveRight Unit:=wdCell
    Selection.TypeText Text:="100"
    Selection.MoveRight Unit:=wdCell
    Selection.TypeText Text:="110"
    Selection.MoveRight Unit:=wdCell
    Selection.TypeText Text:="2/1/98"
    Selection.MoveRight Unit:=wdCell
    Selection.TypeText Text:="400"
    Selection.MoveRight Unit:=wdCell
    Selection.TypeText Text:="135"
    Selection.MoveRight Unit:=wdCell
    Selection.TypeText Text:="3/1/98"
    Selection.MoveRight Unit:=wdCell
    Selection.TypeText Text:="900"
    Selection.MoveRight Unit:=wdCell
    Selection.Font.Bold = wdToggle
    Selection.TypeText Text:="Total Due"
    Selection.MoveRight Unit:=wdCell
    Selection.MoveRight Unit:=wdCell
    Selection.Paste
    Selection.MoveUp Unit:=wdLine, Count:=1
    Selection.HomeKey Unit:=wdLine
    Selection.MoveDown Unit:=wdLine, Count:=3, Extend:=wdExtend
    Selection.MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdExtend
    Selection.Delete Unit:=wdCharacter, Count:=1
    Selection.Font.Bold = wdToggle
    Selection.TypeText Text:="1,400"
    Selection.MoveUp Unit:=wdLine, Count:=3
    Selection.HomeKey Unit:=wdLine
    Selection.MoveDown Unit:=wdLine, Count:=3, Extend:=wdExtend
    Selection.ParagraphFormat.Alignment = wdAlignParagraphRight
End Sub

Syntactical Differences

One of the immediate problems in looking at this macro code is that it is in a different format than the Excel code. For instance, take the following line of code:

Selection.TypeText Text:="Total Due"

What in blazes is that? Well, here's where the help file comes in. A quick trip to the WordBasic help file shows that there are two syntaxes (in other words, you can call the same method or function in two different ways). In one, you can use named arguments as you see above. However, you can also specify the arguments as standard parameters. So, I could easily use the following syntax:

Selection.TypeText("Total Due")

What's the difference? Well, when you use named arguments, you do not have to worry about the order of parameters. It's no big deal, but it does mean that you have more work to do in getting this macro to work than you did in Excel.

NOTE
For the record, the commands I have used from generated code seem to include the names arguments in the order they need to appear as parameters. I don't count on that, though, and always check.

Select What?

The next obvious thing that comes out is the selection object. In Excel, where it's easy to discern one piece of data from another, you have a cell object. Selection is more appropriate to a word processor and deals with the selected text. If you have no selected text, it deals with the location of the cursor.

A Final Look at the Macro

Now you can start taking the macro apart and turning it into COM instructions for WinWord.

Before you do that, though, I need to confess something to you. If you look at the generated code, you will see oddities. Take this code, for example:

Selection.TypeText Text:= _
        "breakdown you deadbeat. Pay up or we will have our friendj G the "
    Selection.TypeText Text:="legbreaker"
    Selection.MoveLeft Unit:=wdCharacter, Count:=15
    Selection.MoveLeft Unit:=wdCharacter, Count:=3, Extend:=wdExtend
    Selection.Delete Unit:=wdCharacter, Count:=3
    Selection.EndKey Unit:=wdLine
    Selection.TypeText Text:=" visit you and break your kneecaps."

This is a case of a typo being created and corrected. See the phrase friendj G? The original letter just said friend, so I had to go back and fix the accidental typing. I could have edited the macro prior to including it in this chapter, but I wanted you to see this. A secretary, I'm not. Although I type fast, I make a number of mistakes (this is one author who is grateful for spell check). If you're like me, plenty of this stuff happens when you're recording macros, and you'll need to correct it later, so you might as well get used to the idea up front.

The Fixed Macro

After working with the help file to get the alternative syntax and modifying the code in the macro to make it legible to Visual FoxPro, I came up with the code shown in Listing 21.7.


Listing 21.7  21CODE07-Visual FoxPro 6 Program Containing a Converted Word Macro
*-- Program....: WORD1.PRG
*-- Version....: 1.0
*-- Author.....: Menachem Bazian, CPA
*-- Date.......: August 23, 1998
*-- Project....: Using Visual FoxPro 6 Special Edition
*-- Notice.....: Copyright (c) 1998 Menachem Bazian, CPA, All Rights Reserved.
*-- Compiler...: Visual FoxPro 06.00.8141.00 for Windows
*-- Abstract...:
*-- Changes....:

*-- Word macro modified for Visual FoxPro
#INCLUDE msWord.h
#DEFINE True .T.
#DEFINE False .F.

*-- Step 1 -- get a reference to MS Word

LOCAL loWord, loTable, lnRow, lnColumn
loWord = GetCOMInstance("word.application")
loWord.Visible = .t.

*-- Now, build the letter

WITH loWord
     .Documents.Add("Normal", False)

     WITH .Selection

          .TypeText("Boyd, Dewey, Cheatem, and Howe")
          .HomeKey(wdLine,wdExtend)
          .Font.Bold = wdToggle
          .ParagraphFormat.Alignment = wdAlignParagraphCenter
          .EndKey(wdLine)
          .TypeParagraph
          .TypeText("Attorneys at Out Law")
          .TypeParagraph
          .TypeText("111 Sewer Avenue")
          .TypeParagraph
          .TypeText("Ratville City, NJ 0700")
          .TypeParagraph
          .Font.Bold = wdToggle
          .ParagraphFormat.Alignment = wdAlignParagraphLeft
          .TypeParagraph
          .InsertDateTime("MMMM d, yyyy", False)
          *    .MoveDown(wdLine, 1)
          .TypeParagraph
          .TypeParagraph
          .TypeText("Joe PayShlepper")
          .TypeParagraph
          .TypeText("521 DeadBroke Avenue")
          .TypeParagraph
          .TypeText("Ratville City, NJ 07001")
          .TypeParagraph
          .TypeParagraph
          .TypeText("Dear Joe,")
          .TypeParagraph
          .TypeParagraph
        
          *-- Up until this point, all I have really done is reformat the code
          *-- from the macro (with deleting some extraneous commands that we
          *-- didn't need,such as commands generated by my switching windows
          *-- while originally generating the macro). Now, I have our first real
          *-- modification to the macro code. Instead of many TypeText calls for
          *-- the one paragraph,I changed it to one call.
          *--       
          *-- As a general rule, the more calls you can eliminate and combine,
          *-- the faster your execution speed will be.

          lcText = "Our records show that you owe us a lot of money. Here's the
               " + ;
               "breakdown you deadbeat. Pay up or we will have our friend the" +
               ;
               " legbreaker visit you and break your kneecaps."

          .TypeText(lcText)
          .TypeParagraph
          .TypeParagraph
          .TypeText("Get the idea?")
          .TypeParagraph
          .TypeParagraph
          .TypeText("Very sincerely yours,")
          .TypeParagraph
          .TypeParagraph
          .TypeText("Boyd, Dewey, Cheatem, and Howe")
          .TypeParagraph
          .TypeParagraph
     ENDWITH

     *-- Now to the next major modification. In the original macro, the table
     *-- was created by inserting a 2 X 2 table and then "typing" the text.
     *-- A table, however, is an object. Using the Table object, you can more
     *-- directly populate and manipulate the table.
     *--
     *-- Why do I care about the table object? I did not, after all, bother to
     *-- look for a more elegant way to do the body of the letter. Well, the
     *-- is obvious when you look at the code. By using the table object, I can
     *-- the code to populate the table generic. When this program is modified
     *-- again to make it fully generic (and get the information from the
     *-- firm's customer and transaction tables), the infrastructure exists.
     *--
     *-- Oh, by the way. Look at the code to work with the cells in the table.
     *-- Doesn't it seem similar to Excel?

     .ActiveDocument.Tables.Add(.Selection.Range, 5, 3)
     loTable = .ActiveDocument.Tables(1)
  
     DECLARE laTableData[5,3]
     laTableData[1,1] = "Invoice #"
     laTableData[1,2] = "Invoice Date"
     laTableData[1,3] = "Amount Due"
  
     laTableData[2,1] = "100"
     laTableData[2,2] = "1/1/98"
     laTableData[2,3] = "100"

     laTableData[3,1] = "110"
     laTableData[3,2] = "2/1/98"
     laTableData[3,3] = "400"
  
     laTableData[4,1] = "135"
     laTableData[4,2] = "3/1/98"
     laTableData[4,3] = "900"
  
     laTableData[5,1] = "Total Amount Due"
     laTableData[5,2] = ""
     laTableData[5,3] = "1,400"
  
     WITH loTable
          FOR lnRow = 1 TO ALEN(laTableData, 1)
               FOR lnColumn = 1 TO  ALEN(laTableData, 2)
                    .Cell(lnRow,lnColumn).Range.InsertAfter(laTableData[lnRow,
                     lnColumn])
               ENDFOR
          ENDFOR
       
          *-- Our table is populated here. Now, all we have to do is autoformat
          *-- it. In case you were wondering.... I cheated... I autoformatted a
          *-- table, captured it in a macro, and then converted the macro code
          *-- here.
       
         loTable.AutoFormat(wdTableFormatClassic4, ;
                                  True, ;
                                  True, ;
                                  True, ;
                                  True, ;
                                  True, ;
                                  True, ;
                                  False, ;
                                  False, ;
                                  True)
                               
     ENDWITH
  
     *-- You need a page break between letters...
  
     .Selection.InsertBreak(wdPageBreak)
ENDWITH

*-- And that, as they say, is that.

RETURN

A closer look at the code shows that only one section has been radically modified from the original code generated by WinWord. That's the section that generates the table. The changes, and the reasons for them, are documented in the code.

The Next Step

You're still not done, but the rest is the proverbial piece of cake. You now have a program that will generate one letter for a specific client, but you need to make it more generic. What can be so difficult about that? After you have this program working, the last step is a simple matter of pulling the information out of the company's tables, replacing the explicit text with fields or variables, and that should do it.

To illustrate this, look at word2.prg, which assumes that you have two tables, Customers and Invoices. These two tables are used to generate the letters for the clients.

The structures of Customers and Invoices and their contents are shown in Listing 21.8.


Listing 21.8  21CODE08-Structure and Contents of Sample Database Tables
Structure for table:                         CUSTOMERS.DBF
  Number of data records:     3    
Date of last update:          08/24/98
  Code Page:                  1252 
   Field   Field Name     Type           Width    Dec  Index   Collate    Nulls
       1   CCUSTNO        Character      10                               No
       2   CORGNAME       Character      35                               No
       3   CCONTACT       Character      35                               No
       4   CSALUT         Character      10                               No
       5   CADD1          Character      30                               No
       6   CADD2          Character      30                               No
       7   CCITY          Character      15                               No
       8   CSTATE         Character       2                               No
       9   CZIP           Character      10                               No
** Total **                             178


Record #:         1

Ccustno  1
Corgname PayShlepper Enterprises
Ccontact Joe PayShlepper
Csalut   Joe
Cadd1    521 DeadBroke Avenue
Cadd2
Ccity    RatVille City
Cstate   NJ
Czip     07001

Ccustno  2
Corgname Owealot Industries
Ccontact G. I. Owealot
Csalut   G. I.
Cadd1    100 Owealot Way
Cadd2    Penthouse Suite
Ccity    New York
Cstate   NY
Czip     10111

Ccustno  3
Corgname PayNot Garments
Ccontact D. Ed Beat
Csalut   Ed
Cadd1    1022 WontPay Drive
Cadd2
Ccity    Moscow
Cstate   NY
Czip     10000

Structure for table:                         INVOICES.DBF
  Number of data records:     9    
Date of last update:          08/24/98
  Code Page:                  1252 
   Field   Field Name     Type           Width    Dec      Index Collate   Nulls
       1   CCUSTNO        Character      10                Asc   Machine     No
       2   CINVNO         Character      10                                  No
       3   DINVDATE       Date           8                                   No
       4   NAMOUNT        Numeric        10       2                          No
** Total **                              39


Record #:         1

Ccustno  1
Cinvno   100
Dinvdate 01/01/98
Namount    100.00

Ccustno  1
Cinvno   110
Dinvdate 02/01/98
Namount    400.00

Ccustno  1
Cinvno   135
Dinvdate 03/01/98
Namount    900.00

Ccustno  2
Cinvno   101
Dinvdate 01/01/98
Namount   1600.00

Ccustno  2
Cinvno   115
Dinvdate 02/15/98
Namount   2235.77

Ccustno  2
Cinvno   146
Dinvdate 03/01/98
Namount   2200.00

Ccustno  2
Cinvno   165
Dinvdate 04/01/98
Namount    500.00

Ccustno  2
Cinvno   199
Dinvdate 05/01/98
Namount   2722.00

Ccustno  3
Cinvno   111
Dinvdate 02/01/98
Namount   2233.98

Listing 21.9 contains the code for Word2.prg.


Listing 21.9  21CODE09.prg-Program That Generates a Letter for Each Customer Record
*-- Program....: WORD2.PRG
*-- Version....: 1.0
*-- Author.....: Menachem Bazian, CPA
*-- Date.......: August 24, 1998
*-- Project....: Using Visual FoxPro 6 Special Edition
*-- Notice.....: Copyright (c) 1998 Menachem Bazian, CPA, All Rights Reserved.
*-- Compiler...: Visual FoxPro 06.00.8141.00 for Windows
*-- Abstract...:
*-- Changes....:

*-- This is Word1.PRG modified to get the data from the firm's tables
*-- For all intents and purposes, the strategy of Word1.prg remains intact.
*-- However, some key modifications are present in this program:
*--
*-- 1. The functionality for building a paragraph has been moved into its
*--    own procedure.
*--
*-- 2. The mechanism for generating the letter has been moved into its own
*--    procedure.
*--
*-- 3. Certain additional functionality is required because we are dealing with
*--    data. The two key modifications is moving the addition of the page break
*--    to the beginning of the letter for a customer (if this is not the first
*--    customer letter) and passing the record number so we can get access to
*--    the right table object for a customer.
*--
*-- Finally, most of the comments from Word1.PRG that related to the upgrade of
*-- the code from VBA to VFP has been removed.

*-- Word macro modified for Visual FoxPro
#INCLUDE msWord.h
#DEFINE True .T.
#DEFINE False .F.

*-- Open the tables. We are assuming, for the purposes of this example,
*-- that the tables are the result of processing and represents the information
*-- be presented in the letters (i.e., I will not do any processing on the
*-- data other than what is needed to populate the Word letters).

*-- Step 1 -- get a reference to MS Word

LOCAL loWord, loTable, lnRow, lnColumn
loWord = GetCOMInstance("word.application")
loWord.Visible = .t.
loWord.Documents.Add
loWord.DisplayAlerts = .F.

*-- In order to make the code a little easier to modify and work with,
*-- I am moving the code that actually generates the letter to a procedure.
*--
*-- I will be sendind the procedure an object with the customer information
*-- and an array with the invoice information. As you will see, it will make
*-- some of the letter processing even easier.

CLOSE DATA ALL
USE customers
USE invoices IN 0

SELECT customers
SCAN
     SCATTER NAME oCust

     SELECT invoices.cInvNo, ;
          DTOC(invoices.dInvDate), ;
          invoices.nAmount ;
          FROM invoices ;
          WHERE invoices.cCustNo = oCust.cCustNo ;
          ORDER BY 1 ;
          INTO ARRAY laInvoices

     DO genletter WITH loWord, oCust, laInvoices, RECNO()
ENDSCAN


*------------------------------------------------------
* Procedure...: GenLetter
* Called by...: Word2
*
* Abstract....: Actually generates the word letter
*
* Parameters..: toWord - A reference to the word COM server.
*             : toCust - The customer information
*             : taInvoices - Array of open invoices for this customer
*             : tnRecno - Customer record number
*
* Notes.......:
*------------------------------------------------------
PROCEDURE genletter(toWord, toCust, taInvoices, tnRecno)

     *-- Now, build the letter

     WITH toWord

          WITH .Selection
               IF tnRecno > 1
                    *-- You need a page break between letters... Only add the
                    *-- page break starting with the second letter.

                    .InsertBreak(wdPageBreak)
               ENDIF
            
               *-- Note that all text typing and paragraph mark functionality
               *-- has been moved to AddParagraph. This saves on the code             
               *-- duplication.
            
               .Font.Bold = .T.
               .ParagraphFormat.Alignment = wdAlignParagraphCenter
               AddParagraph(toWord, "Boyd, Dewey, Cheatem, and Howe")
               AddParagraph(toWord, "Attorneys at Out Law")
               AddParagraph(toWord, "111 Sewer Avenue")
               AddParagraph(toWord, "Ratville City, NJ 0700")
               .Font.Bold = .F.
               .ParagraphFormat.Alignment = wdAlignParagraphLeft
               AddParagraph(toWord)
               .InsertDateTime("MMMM d, yyyy", False)
               AddParagraph(toWord,"",2)

               *-- Insert the customer information

               IF !EMPTY(toCust.cContact)
                    AddParagraph(toWord, ALLTRIM(toCust.cContact))
               ENDIF

               IF !EMPTY(toCust.cOrgName)
                    AddParagraph(toWord, ALLTRIM(toCust.cOrgName))
               ENDIF

               IF !EMPTY(toCust.cAdd1)
                    AddParagraph(toWord, ALLTRIM(toCust.cAdd1))
               ENDIF

               IF !EMPTY(toCust.cAdd2)
                    AddParagraph(toWord, ALLTRIM(toCust.cAdd2))
               ENDIF

               LOCAL lcCSZ
               lcCSZ = ALLTRIM(toCust.cCity) + ", " + ;
                    toCust.cState + " " + ALLT(toCust.cZIP)

               IF !EMPTY(lcCSZ)
                    AddParagraph(toWord, ALLTRIM(lcCSZ))
               ENDIF

               AddParagraph(toWord)
               AddParagraph(toWord, "Dear " + ALLTRIM(toCust.cSalut) + ",", 2)

               lcText = "Our records show that you owe us a lot of money. Here's
               the " + ;
                    "breakdown you deadbeat. Pay up or we will have our friend
                     the" + ;
                    " legbreaker visit you and break your kneecaps."

               AddParagraph(toWord, lcText, 2)
               AddParagraph(toWord, "Get the idea?", 2)
               AddParagraph(toWord, "Very sincerely yours,", 2)
               AddParagraph(toWord, "Boyd, Dewey, Cheatem, and Howe", 2)
          ENDWITH

          *-- Note how the table size is now based on the size of taInvoices...

          LOCAL lnRows, lnCols, lnCounter, lnCount2, lnRow, loTable, lnTotal
          lnRows = ALEN(taInvoices, 1)
          lnCols = ALEN(taInvoices, 2)

          .ActiveDocument.Tables.Add(.Selection.Range, lnRows + 2, lnCols)
       
          *-- Each customer has one table... The record number of the customer
          *-- will get us the right table in this case.
       
          loTable = .ActiveDocument.Tables(tnRecno)

          DECLARE laTableData[lnRows + 2, lnCols]
          laTableData[1,1] = "Invoice #"
          laTableData[1,2] = "Invoice Date"
          laTableData[1,3] = "Amount Due"

          FOR lnCounter = 1 TO lnRows
               FOR lnCount2 = 1 TO lnCols
                    laTableData[lnCounter + 1, lnCount2] = taInvoices[lnCounter,
                    lnCOunt2]
               ENDFOR
          ENDFOR

          *-- Now the totals line

          laTableData[lnRows + 2, 1] = "Total Amount Due"
          laTableData[lnRows + 2, 2] = ""
       
          *-- Do a quick sum here
          lnTotal = 0
          FOR lnCOunter = 1 TO ALEN(taInvoices, 1)
               lnTotal = taInvoices[lnCounter, 3] + lnTotal
          ENDFOR
       
          laTableData[lnRows + 2, 3] = lnTotal

          WITH loTable
               FOR lnRow = 1 TO ALEN(laTableData, 1)
                    FOR lnColumn = 1 TO  ALEN(laTableData, 2)

                         *-- Processing is slightly different for the third
                         *-- column. If we have a number, we want the number
                         *-- formatted properly.
                         *--
                         *-- Also, we want it right aligned.
                      
                         IF lnColumn = 3
                              IF TYPE("laTableData[lnRow, lnColumn]") = "N"
                                   .Cell(lnRow,lnColumn).Range.InsertAfter( ;
                                        TRANSFORM(laTableData[lnRow, lnColumn],
                                        "999,999.99"))
                              ELSE
                                   .Cell(lnRow,lnColumn).Range.InsertAfter( ;
                                        laTableData[lnRow, lnColumn])
                              ENDIF
                           
                              .Cell(lnRow,lnColumn).Range.ParagraphFormat.Alignment
                                        = ;
                                   wdAlignParagraphRight
                         ELSE
                              .Cell(lnRow,lnColumn).Range.InsertAfter( ;
                                   laTableData[lnRow, lnColumn])
                         ENDIF
                    ENDFOR
               ENDFOR

               *-- Our table is populated here. Now, all we have to do is
               *-- autoformat it. In case you were wondering.... I cheated... I
               *-- autoformatted a table, captured it in a macro and then
               *-- converted the macro code here.

               .AutoFormat(wdTableFormatClassic4, ;
                    True, ;
                    True, ;
                    True, ;
                    True, ;
                    True, ;
                    True, ;
                    False, ;
                    False, ;
                    True)

          ENDWITH


          *-- Move to the end of the document.

         .Selection.EndKey(wdStory)
     ENDWITH

     *-- And that, as they say, is that.


     RETURN
ENDPROC &&* GenLetter

*------------------------------------------------------
* Procedure...: AddParagraph
* Called by...: GenLetter
*
* Abstract....: Adds a paragraph
*
* Parameters..:
*
* Notes.......:
*------------------------------------------------------
PROCEDURE AddParagraph(toWord, tcText, tnParagraphs)
     LOCAL lnCounter

     WITH toWord.Selection
          IF PCOUNT() >= 2
               .TypeText(tcText)
          ENDIF

          IF PCOUNT() < 3 OR TYPE("tnParagraphs") # "N"
               tnParagraphs = 1
          ENDIF

          FOR lnCounter = 1 TO tnParagraphs
               .TypeParagraph
          ENDFOR
     ENDWITH

     RETURN
ENDPROC &&* AddParagraph

A Final Word

I am sure that there are those who might look at the way in which these letters were generated and opine that there are better ways to do the task. Subjects like templates, running macros within Word, and more might come up.

The point behind this material was not to show you exactly how to use Word in your applications, but to take you through the learning process. The process whereby you learn how to use Word and Excel is more important than anything that can be documented within one chapter of a book.


© Copyright, Sams Publishing. All rights reserved.