Special Edition Using Visual FoxPro 6
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.
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.
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.
){kind=link}
){kind=link}
){kind=link}
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.
* 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.
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.
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.
){kind=link}
){kind=link}
*-- 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.
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.
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.
){kind=link}
*-- 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
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.
*-- 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
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.
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.
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.
){kind=link}
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
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. |
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.
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.
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.
*-- 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.
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.
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.
*-- 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
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.