Creating a Word Report from Database Information

Most company reports are based on a customized template containing predefined text styles and formatting. In our example, the Access database generates a report by creating a new Word document based on a formatted Word template. (This template, EnerRpt.dot, can be found in the Chapter 8 practice folder.) As you'll see, the Energy Report template is a one-row table with preset headers. As you retrieve each row of data from the database, you add a new row containing the database information and some calculated entries to the table. When you've imported all the data to the table, a final row containing the totals from two columns in the table is automatically added.

1. In the Visual Basic Editor, click Module on the Insert menu to create a new code module.
2. Add the following declarations to the code module:

Dim m_appWd As New Word.Application Dim m_docReport As Word.Document

The first declaration declares the module-level variable m_appWd as a new instance of the Word Application object. The use of the keyword New in the declaration statement indicates that the first time you use the variable m_appWd in code, a new instance will be created. Consequently, you won't need to use the CreateObject function. The m_appWd variable is a module-level variable because it's used in the procedure AddTotalRow to set the Visible property of the Word application window to True once you generate the report. You'll create the AddTotalRow procedure in the section of this chapter titled "Format the Final Row in the Word Table."

The second declaration declares the module-level variable m_docReport as a Word Document object, which is used by each of the procedures created in the current code module.

3. Add the following CreateWordDocument procedure after the variable declarations:

Sub CreateWordDocument() Set m_docReport = m_appWd.Documents.Add(Template:= _ g_sDBProjectPath & "EnerRpt.dot", NewTemplate:=False) With m_docReport .BuiltinDocumentProperties("Subject").Value = g_sCircuit .Fields.Update End With End Sub

The CreateWordDocument procedure sets the module-level variable m_docReport to the newly added Word Document object. You base the new Word document on the template EnerRpt.dot, which is found in the Chapter 8 practice folder. The first line within the With…End block in the CreateWordDocument procedure sets the built-in document property Subject to the circuit under investigation. In the code module modMain, you set the public variable g_sCircuit to "Computer Network." You can easily add new circuits to the investigation in the future. The second line (.Fields.Update ) in the With…End block updates all of the fields in the Word document. One field, named Circuit, was added to the Energy Report template, EnerRpt.dot, beside the title "Energy Usage Chart"; this field contains the Subject document property.

1. Click beneath the procedure CreateWordDocument and create a new procedure called AddToTable by adding the following code:

Sub AddToTable(sID As String, sDateTime As String, _ sComputer As String, sgPeriodkWh As Single, _ sgCost As Single) With m_docReport.Tables(1) With .Rows.Last .Cells(1).Range.Text = sID .Cells(2).Range.Text = sDateTime .Cells(3).Range.Text = sComputer .Cells(4).Range.Text = sgPeriodkWh .Cells(5).Range.Text = Format$(sgCost, "###0.00") End With .Rows.Add End With End Sub

Five arguments are passed to the AddToTable procedure. You set the values of the arguments in the GetDatabaseInfo procedure in the code module modMain. You use the With…End block added within the AddToTable procedure to access the first (and only) table in the Word document created in the CreateWordDocument procedure.

This code's With…End block adds values to each cell in the last row of the Word table. Each cell in a row in a table has a text range, represented by the Range object. The Range object allows you to access the Text property so that you can assign a text string to the cell. In the last line within the With…End block, you format the text string assigned to the cell text by using the built-in Visual Basic function Format$. The Format$ function formats a string according to the second argument in the function. This is similar to formatting cells in Excel.

After data from the database is added to the cells of the last row in the table, the line .Rows.Add adds a new row to the table in the Word document. That way, data from the next record in the database can be added to the table. Your code should match that in the following illustration:

1. Click beneath the procedure AddToTable and create a new procedure called AddTotalRow by adding the following code:

Sub AddTotalRow() With m_docReport.Tables(1).Rows.Last .Range.Bold = True .Range.Font.Size = 12 .Borders.Item(wdBorderTop) _ .LineStyle = wdLineStyleDouble End With End Sub

The AddTotalRow procedure contains a With…End block that formats the last row in the Word table. The text range in the cells of the last row are made bold and set to a font size of 12 point. The procedure also sets the border style at the cells' top edge to double lines.

2. Above the End With statement, in the AddTotalRow procedure, add the following code:

.Cells(1).Range.Text = "Total" .Cells(3).Range.Text = g_iTotalHours .Cells(5).Range.Text = Format$(g_sgTotalCost, "###0.00")

The text of the first cell in the table's last row is "Total." You set the third cell's text to the total number of hours the stations in the computer network were turned on. The fifth cell indicates the total energy cost for the period under investigation. The public variables g_iTotalHours and g_sgTotalCost are calculated in the GetDatabaseInfo procedure in the code module modMain.

3. After the With…End block, above End Sub, add the following two lines to save the document with the filename "Report," and to display the Word application window:

m_docReport.SaveAs g_sDBProjectPath & "Report.doc" m_appWd.Visible = True

4. In the Properties window in the Visual Basic Editor, change the Name property of the code module to modWord.
5. Click the Save button on the Standard toolbar in the Visual Basic Editor to save changes to the code.


Save

You've now added all the code for creating a Word document based on a predefined template and for adding values to the table within the document.

1. Copy the Word template file EnerRpt.dot from the Chapter 8 practice folder on the CD for this book to the same folder as the MyEnergy database. If this file is already located in this folder, you can skip this step.
2. Double-click the modMain project item in the Project Explorer to make the code window active.
3. In the modMain code module, add the following procedure.

Sub TestCreatingWordDocument() g_sDBProjectPath = Application.CurrentProject.Path & "\" modWord.CreateWordDocument GetDatabaseInfo bReport:=True, bSheet:=False, _ bPres:=False, bUpdateForm:=False modWord.AddTotalRow End Sub

Note the values of the arguments passed to the GetDatabaseInfo procedure. You set the first argument, bReport, to True in order to create a report in Word. You set the second and third arguments, bSheet and bPres, to False because an Excel spreadsheet and chart and a PowerPoint presentation are not required. You set the last argument, bUpdateForm, to False because the Access form is not displayed.

When you run the procedure above, the document should appear as follows: