HOWTO: Create Categorized Table in Word 97 w/ OLE AutomationLast reviewed: February 12, 1998Article ID: Q180901 |
The information in this article applies to:
SUMMARYThis article demonstrates how to create and format a table in Microsoft Word 97 from Visual FoxPro for Windows 5.0 and 5.0a using OLE automation. The table contains a grouping field, for instance, a state, company or customer sales ID. This example groups by country. The data for the Word document will be gathered using an SQL query into a temporary cursor.
MORE INFORMATIONFor this code to work correctly, the first field in the query must be the group field used in the Word 97 report. For instance, if it is necessary to use all fields in a table for the report, but the field that is to be the group field is not the first field in the source table, then issue a query similar to this:
SELECT state,* FROM Customer GROUP BY state, custid INTO CURSOR categoryThis forces the group field, state in this example, to be the first field in the queried result. NOTE: This code only works with the Service Release-1 for Word 97. Using prior versions of Word 97 causes a "Type Mismatch" and other OLE errors. Create a program and enter the code given below. When the code is executed, the user will see two prompts. These prompts ask the user how the Word document should be formatted. The first prompt asks whether the user wants to have each grouping on its own page or not. The second question asks if the user wants underlines between the rows of the table or not. These questions are merely for viewing preferences and to show how the programmer may format the table in Word with these and other preferences. Here is the sample code:
*/ Begin program code /*
CLEAR
SET TALK OFF
SET CONSOLE OFF
ctempfield = "" && Variable to hold group category.
headings_added = .F.
newgrouppage = .F.
* Make sure the FIRST field in the SELECT is the field the report
* is categorized by. The Customer table is located in the
* \VFP\Samples\Data folder.
SELECT country, company, contact, title,maxordamt,phone;
FROM HOME()+"Samples\Data\testdata!customer" GROUP BY;
country,cust_id INTO CURSOR category
IF _TALLY > 0
oWord = CREATEOBJECT("Word.Application")
oWord.Documents.Add
owRange = oWord.Activedocument.Range(0,0)
numcols = FCOUNT()-1 && Get number of fields for detail section
oWord.Activedocument.Tables.Add(owRange, 1, numcols)
* First prompt, separate pages for each group.
nanswer = messagebox("Put each group on a new page?",36,;
"Sepatate Pages")
DO CASE
CASE nanswer = 6 && Yes
newgrouppage = .T.
CASE nanswer = 7 && No
headings_added = .F.
ENDCASE
WAIT WINDOW "Please wait while the data is formatted in Word.";
+ CHR(13)+"This may take several minutes..." NOWAIT
DO WHILE !EOF()
ctempfield = EVAL(FIELD(1)) && Set 1st field in table as category
WITH oWord
.Selection.Font.Reset
.Selection.TypeText(EVAL(FIELD(1)))
.Selection.SelectRow
.Selection.ParagraphFormat.Alignment = 1
.Selection.Font.Name = "Arial"
.Selection.Font.Size = 16
.Selection.Font.Bold = .T.
.Selection.SelectRow
.Selection.Cells.Merge
.Selection.MoveRight(12)
.Selection.Cells.Split(1,numcols)
IF NOT headings_added && Put at least one heading in document
.Selection.MoveRight(12)
FOR i = 2 TO FCOUNT()
.Selection.Font.Italic = .T.
.Selection.ParagraphFormat.Alignment = 1
.Selection.Font.Name = "Times New Roman"
.Selection.Font.Size = 8
.Selection.TypeText((FIELD(i)))
.Selection.MoveRight(12)
headings_added = .T.
ENDFOR
ENDIF
FOR i = 2 TO FCOUNT()
curfield = EVAL(FIELD(i))
* Check data type. Does not check Double, Float, Integer, General, Memo.
IF TYPE((FIELD(i)))<>"C"
DO CASE
CASE TYPE((FIELD(i))) = "D" && Date field
curfield = DTOC((FIELD(i)))
CASE TYPE((FIELD(i))) = "N" && Numerical
curfield = STR((FIELD(i)))
CASE TYPE((FIELD(i))) = "Y" && Currency
curfield = STR(EVAL(FIELD(i)),8,2)
CASE TYPE((FIELD(i))) = "L" && Logical
IF curfield
curfield = "True"
ELSE
curfield = "False"
ENDIF
CASE TYPE((FIELD(i))) = "T" && DateTime
curfield = TTOC(EVAL(FIELD(i)))
ENDCASE
.Selection.Font.Reset
.Selection.TypeText(curfield)
ELSE
.Selection.Font.Reset
.Selection.TypeText(curfield)
ENDIF
.Selection.Font.Reset
.Selection.MoveRight(12)
ENDFOR
SKIP
ENDWITH
DO WHILE ctempfield = EVAL(FIELD(1)) && Get other like records.
WITH oWord
FOR i = 2 TO FCOUNT()
curfield = EVAL(FIELD(i))
IF TYPE((FIELD(i)))<>"C"
DO CASE
CASE TYPE((FIELD(i))) = "D"
curfield = DTOC((FIELD(i)))
CASE TYPE((FIELD(i))) = "N"
curfield = STR((FIELD(i)))
CASE TYPE((FIELD(i))) = "Y"
curfield = STR(EVAL(FIELD(i)),8,2)
CASE TYPE((FIELD(i))) = "L"
IF curfield
curfield = "True"
ELSE
curfield = "False"
ENDIF
CASE TYPE((FIELD(i))) = "T"
curfield = TTOC(EVAL(FIELD(i)))
ENDCASE
.Selection.TypeText(curfield)
ELSE
.Selection.TypeText(curfield)
ENDIF
.Selection.MoveRight(12)
ENDFOR
ENDWITH
SKIP
ENDDO
IF RECNO() > RECCOUNT() && Prevents an empty table/cells.
EXIT
ELSE
IF newgrouppage
headings_added = .F. && False: add headings to each page.
oWord.Selection.InsertBreak(2) && Page break each category.
ENDIF
ENDIF
ENDDO
oWord.Selection.SelectRow && Ensures no extra rows in the table.
oWord.Selection.Rows.Delete
* This section underlines or turns off all lines in the table.
nanswer = MESSAGEBOX("Turn off underlines Y/N",36,;
"No underlines in the table?")
DO CASE
CASE nanswer = 6 && Yes, turn off all underlines.
WAIT WINDOW 'Formating table with no underlines in the table.';
NOWAIT
WITH oWord
For Each aTable In .ActiveDocument.Tables && Format all tables.
aTable.Borders(-1).LineStyle = 0 && Top border.
aTable.Borders(-2).LineStyle = 0 && Left
aTable.Borders(-3).LineStyle = 0 && Bottom
aTable.Borders(-4).LineStyle = 0 && Right
aTable.Borders(-5).LineStyle = 0 && Horizontal
aTable.Borders(-6).LineStyle = 0 && Vertical
aTable.Borders.Shadow = 0
ENDFOR
ENDWITH
CASE nanswer = 7 && Number just underlines.
WITH oWord
WAIT WINDOW 'Formating table with underlines between records.';
NOWAIT
For Each aTable In .ActiveDocument.Tables && Format each table.
aTable.Borders(-1).LineStyle = 0 && Top border
aTable.Borders(-2).LineStyle = 0 && Left
aTable.Borders(-3).LineStyle = 1 && Bottom
aTable.Borders(-4).LineStyle = 0 && Right
aTable.Borders(-5).LineStyle = 1 && Horizontal
aTable.Borders(-6).LineStyle = 0 && Vertical
aTable.Borders.Shadow = 0
ENDFOR
ENDWITH
oWord.ActiveWindow.View.TableGridlines = .F. && No table gridlines
ENDCASE
* Get the number of pages in the Word report. The code adds the report
* headings to the document header when the user chooses not to have the
* report categories print on separate pages. Makes viewing groups
* headings easier on other pages.
numpages = oWord.ActiveDocument.ComputeStatistics(2)
IF numpages > 1 AND NOT newgrouppage
WITH oWord
.Selection.MoveDown
.ActiveWindow.ActivePane.View.Type = 3 && Put Word in Page view
.ActiveWindow.ActivePane.View.SeekView = 9 && Open header.
.Selection.ParagraphFormat.TabStops.ClearAll && Clear tabs.
* Printed header width is computed by subtracting margins
* from page width. The margins are divided by 72. Word stores
* these values as points; i.e. 72points/inch.
pagewidth = 8.5-(.ActiveDocument.PageSetup.RightMargin+;
.ActiveDocument.PageSetup.LeftMargin)/72
tabspace = (pagewidth/(numcols))*72 && Convert inches to points
tabstops = tabspace
FOR i = 2 TO FCOUNT()
.Selection.Font.Italic = .T. && Format heading captions.
.Selection.ParagraphFormat.Alignment = 1
.Selection.Font.Name = "Times New Roman"
.Selection.Font.Size = 8
.Selection.TypeText((FIELD(i)))
.Selection.TypeText(chr(9)) && Tab to set the next heading.
.Selection.ParagraphFormat.TabStops.Add(tabstops) && Tab
tabstops = tabstops+tabspace
ENDFOR
ENDWITH
ENDIF
WITH oWord
.ActiveWindow.View.Type = 3 && Switch to page view. Normal view=1
.ActiveWindow.ActivePane.View.SeekView = 0 && Open main document.
.Selection.Homekey(6) && Go to top of document.
.Visible = .T. && Make Word visible.
.Application.Activate && Bring Word forward.
.WindowState = 0 && Show Word in normal state. Maximized=1
.ActiveWindow.ActivePane.View.ShowAll = 0 && No nonprinting items.
ENDWITH
ELSE
=MESSAGEBOX("There were no records in the query.",16,;
"Empty Query")
ENDIF
*/ End program code /*
REFERENCESFor more information about obtaining Word for Windows 97 Service Release - 1, please see the following article in the Microsoft Knowledge Base:
ARTICLE-ID: Q172475 TITLE : How to Obtain and Install MS Office 97 SR-1Microsoft Word Visual Basic Help (c) Microsoft Corporation 1998, All Rights Reserved. Contributions by Dean Christopher, Microsoft Corporation
Keywords : FxinteropWinword FxprgGeneral vfoxwin kbcode Technology : ole Version : WINDOWS:5.0,5.0a Platform : WINDOWS Issue type : kbhowto |
================================================================================
© 1998 Microsoft Corporation. All rights reserved. Terms of Use. |