Adding Charts to the Report

We've seen now that our code works. Word opened and the heading section of the report displayed successfully. Now let's return to the VBE and add our charts and their descriptive text.

We have three charts to insert and three sets of descriptive text. In this section, we will add a loop that will do the following:

• Insert a blank line between each section

• Insert the descriptive text

• Insert the chart

1. In the MakeWordDoc subroutine, place the cursor at the beginning of the End With statement.

2. Press Enter.

3. Move the insertion point into the blank line you just created and add the following lines of code:

For i = 1 To 3 .Selection.TypeParagraph InsertText i InsertChart i Next i

I've created a couple of helper functions to insert the text and charts according to the index passed into the procedure. The InsertText subroutine finds the chart with the index value passed in and grabs its title. It then calls the GetSubjectBody function to get the text corresponding to the chart. Finally, it formats the text area and inserts the appropriate section heading and text.

4. On Standard Module1, add the code shown in Listing 8-5.

Listing 8-5. InsertText Procedure

Sub InsertText(Index As Integer) Dim sTitle As String Dim sBody As String

Worksheets(1).ChartObjects(Index).Activate sTitle = ActiveChart.ChartTitle.Text sBody = GetSubjectBody(Index)

With m_oWordApp

.Selection.Style = .ActiveDocument.Styles("Heading 2") .Selection.TypeText sTitle .Selection.TypeParagraph .Selection.TypeText sBody .Selection.TypeParagraph End With End Sub

The InsertChart subroutine finds the chart based upon the index value passed in, and then applies its Copy method to place a copy of the chart on the Windows clipboard. Then we move to the Word document and apply the Paste command at the insertion point.

5. On Standard Module1, add the code shown in Listing 8-6.

Listing 8-6. InsertChart Procedure

Sub InsertChart(Index As Integer) Worksheets(1).ChartObjects(Index).Copy m_oWordApp.Selection.Paste End Sub

This completes the MakeWordDoc procedure. The finished code should look like Listing 8-7.

Listing 8-7. Complete MakeWordDoc Subroutine

Sub MakeWordDoc() Dim i As Integer Dim sTitle As String Dim sBody As String

Set m_oWordApp = CreateObject("Word.Application") Set m_oWordDoc = m_oWordApp.Documents.Add sTitle = GetTitle sBody = GetTitleBody

With m_oWordApp

.Selection.Style = .ActiveDocument.Styles("Heading 1") .Selection.TypeText sTitle .Selection.TypeParagraph .Selection.TypeText sBody For i = 1 To 3 .Selection.TypeParagraph InsertText i InsertChart i Next i End With m_oWordApp.Visible = True MsgBox "word s/b open now" m_oWordApp.Quit Set m_oWordApp = Nothing End Sub

Now let's run the code and see the finished product.

6. Place the insertion point anywhere inside the MakeWordDoc subroutine.

7. Click the Run button on the VBE toolbar or press F5.

The completed report will appear, and should look similar to Figure 8-6.

Figure 8-6. The completed summary report displayed in Print Preview mode

The Word DOM is very rich and contains many other useful objects for you to code against. Once you've added a reference to Word in the VBE, you can explore its many features in the Object Browser (which you can access by pressing F2). Figure 8-7 shows a view of the Word objects as displayed in the Object Browser.

0 0

Post a comment