Microsoft Office 2000/Visual Basic Programmer's Guide

Creating Static Reports in Word

For data analysis, Excel is the obvious choice, but there may be times when you want to display data in Microsoft Word — for example, if you're preparing a memo that contains sales information from an Access database.

Getting Data into Word

To write data to a Word document from an Access database, you can save an Access table, query, form, or report as a Rich Text Format file (.rtf) or an HTML file and open it in Word. From Excel, you can save a worksheet as a text file (.txt) or an HTML file.

The following code fragment writes data from an Access saved query to an RTF file. The data is automatically displayed in a table in the RTF file.

DoCmd.OutputTo acOutputQuery, "Current Product List", acFormatRTF, _
   "Current Product List.rtf"

This code fragment is taken from the InsertDataIntoTable procedure in the GenerateReport.dot sample file, which is available in the ODETools\V9\Samples\OPG\Samples\CH02 subfolder on the Office 2000 Developer CD-ROM.

Manipulating Data in Word

To perform a simple operation on the data in a Word table, such as finding the sum or average of the values in a column, you can insert the appropriate formula in a table cell. For example, the following code fragment prints the average of a column of numbers to a new row in the table.

' Return reference to first table in document.
Set tblData = ThisDocument.Tables(1)
With tblData
   ' Add row to end of table.
   .Rows.Add
   ' Store number of rows.
   lngRowCount = .Rows.Count
   ' Specify formula for cell in first column of last row.
   .Columns(1).Cells(lngRowCount).Formula "=Average(Above)"
End With

If you're performing complex calculations, consider embedding an Excel worksheet in a Word document rather than adding a formula to a Word table.