DOCUMENT:Q180901 18-DEC-2001 [foxpro] TITLE :HOWTO: Create Categorized Table in Word 97 w/ OLE Automation PRODUCT :Microsoft FoxPro PROD/VER::5.0,5.0a,6.0,Service Release 1 (SR-1) OPER/SYS: KEYWORDS:kbcode kbinterop kbvfp500 kbvfp600 kbWord ====================================================================== ------------------------------------------------------------------------------- The information in this article applies to: - Microsoft Visual FoxPro for Windows, versions 5.0, 5.0a, 6.0 - Microsoft Word 97 for Windows, version Service Release 1 (SR-1) ------------------------------------------------------------------------------- SUMMARY ======= This article demonstrates how to create and format a table in Microsoft Word 97 from Visual FoxPro for Windows 5.0 and later using OLE automation. The table contains a grouping field, for instance, a state, company or customer sales ID. This example groups by country or region. The data for the Word document will be gathered using an SQL query into a temporary cursor. MORE INFORMATION ================ For 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 category This 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. In Visual FoxPro 6.0, the Customer table is * in the Microsoft Visual Studio\Common\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 /* REFERENCES ========== For more information about obtaining Word for Windows 97 Service Release - 1, please see the following article in the Microsoft Knowledge Base: Q172475 HOWTO: Obtain and Install MS Office 97 SR-1 Microsoft Word Visual Basic Help (c) Microsoft Corporation 1998, All Rights Reserved. Contributions by Dean Christopher, Microsoft Corporation Additional query words: ====================================================================== Keywords : kbcode kbinterop kbvfp500 kbvfp600 kbWord Technology : kbWordSearch kbVFPsearch kbAudDeveloper kbWord97 kbWord97Search kbZNotKeyword2 kbWord97SR1 kbVFP500 kbVFP600 kbVFP500a Version : :5.0,5.0a,6.0,Service Release 1 (SR-1) Issue type : kbhowto ============================================================================= THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY. Copyright Microsoft Corporation 2001.