Creating a Report in Word

In Chapter 5, we explored charting in Excel 2007. We are going to turn one of our chart reports into a summary report using the Word 2007 DOM from the Excel 2007 VBE. In this example, you'll learn how to do the following:

• Open an instance of Microsoft Word programmatically

• Create a new document within the instance of Word

• Insert chart objects from Excel

Let's start by opening an existing Excel project that contains numeric and chart data. We'll use the charts in our summary report in Word.

1. From the source files for this book, open the file Chapter 8\Files\Chart08.xslm.

The file contains sales data and pie charts for a few product categories that we need to create a report on. The report will cover the first few months of sales year 2007, as shown in Figure 8-1.

Figure 8-1. Sales data and pie charts

2. Open the VBE by selecting the Developer ribbon > Code tab > Visual Basic command, or by pressing Alt+F11.

3. Add a new standard module in the Project Explorer.

Before we can begin accessing and working with the Word DOM, we need to add a reference to Word in the References dialog box.

4. In the VBE, select Tools > References to display the References dialog box (shown in Figure 8-2).

Figure 8-2. References dialog box

5. In the References dialog box, scroll down until you see the Microsoft Word 12.0 Object Library (shown in Figure 8-3).

Vba Excel Knowledge
Figure 8-3. Microsoft Word 12.0 Object Library selected

6. Select the Microsoft Word 12.0 Object Library.

8. Add the following module-level variables:

Private m_oWordApp As Word.Application Private m_oWordDoc As Word.Document

These variables will hold the instance of our Word application and the new Word document for the report.

9. Create a new subroutine named MakeWordDoc.

10. Add the following variable declarations:

Dim i As Integer Dim sTitle As String Dim sBody As String

These will contain a counter value used when we loop through our charts, and two string values to hold the title and introductory text for the report. Next, we'll assign the title of our report, "2007 Sales Report," to the sTitle variable. Since we'll also be creating a PowerPoint presentation from our VBA code, let's create some routines to provide that information to our application. This way, we can reuse it in both processes.

+1 0


Post a comment