HOWTO: Create a Report in Word w/Excel Chart via OLE Automation

ID: Q114378

The information in this article applies to:

- Standard and Professional Editions of Microsoft Visual Basic for

  Windows, version 3.0

- Microsoft Word for Windows, version 6.0 - Microsoft Excel, version 5.0 - Microsoft Office for Windows, version 4.0

SUMMARY

This article demonstrates how to use OLE automation from Visual Basic to create a report in Word for Windows that contains an embedded Excel chart.

MORE INFORMATION

Using OLE Automation, you can programmatically create a chart in Excel and specify where you want to place that chart in a Word document. Although you could programmatically move the insertion point to a specific position within the document, you typically will know ahead of time exactly where you want to put the chart in the document.

By creating a template in Word and placing a bookmark in the template to specify the location of the chart, you can precisely insert the chart at any position within the document.

For specific information that explains how to create a chart in Excel version 5.0 via OLE Automation, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q112417
   TITLE     : HOWTO: Create Excel Chart w/ OLE Automation from Visual
               Basic

Step-by-Step Example

1. Start Microsoft Word version 6.0 for Windows.

2. From the File menu, choose New (ALT, F, N). Then select Template.

3. Insert the text that you want to appear in all reports. Then place the

   cursor where you want the Excel Chart to be inserted.

4. From the Edit menu, choose BookMark (ALT, E, B). Name the bookmark

   Chart1, and click the Add button.

5. Save the Template. From the File menu, choose Save (ALT F, S), giving

   the filename of the template "C:\WINWORD\TEMPLATE\MYREPORT.DOT"

6. Close Word. From the File Menu, choose Exit (ALT, F, X).

7. Start a new project in Visual Basic (ALT, F, N). Form1 is created

   by default.

8. Add a command button (Command1) to Form1.

9. Add the following code to the General Declarations section of Form1:

      Dim oWDBasic As Object
   ' By making this object global to the Form, Word will not close down
   ' once the Command1_Click event is finished. If this variable where
   ' declared within the Click event, the scope of the variable would be
   ' local to the Click event. In which case, when the Click event ended,
' the variable would be de-allocated, and Word would unload without
   ' making needed changes to the document and without saving changes made
   ' by the code in the Click Event.

10. Add the following code to the Command1_Click event procedure:

      Sub Command1_Click ()

         ' Create XLChart and copy it.
         CreateXLChart

         Set oWDBasic = CreateObject("Word.Basic")

         ' Create a new document based on template previously created
         oWDBasic.FileNew "C:\WINWORD\TEMPLATE\MYREPORT.DOT"

         ' Insert chart into document where the bookmark 'Chart1' exists

         oWDBasic.EditGoto "Chart1"
         oWDBasic.EditPaste

         ' Save new document to disk
         oWDBasic.FileSaveAs "C:\WINWORD\REPORT.DOC"

      End Sub

11. Add the following new procedure to the General Declarations section of

    Form1:

    Sub CreateXLChart ()

       Dim objXLsheet As Object ' reference to Excel Worksheet
       Dim objRange1 As Object  ' reference to the first series in chart
       Dim objRange2 As Object  ' reference to the second series in chart
       Dim objChart1 As Object  ' reference to the chart created

       Dim iRow As Integer ' Index variable for the current Row
       Dim iCol As Integer ' Index variable for the current Row

       Dim strTmpRange As String ' Temporarily hold Range in R1C1 notation

       Const cNumCols = 10 ' Number of points in each Series
       Const cNumRows = 2  ' Number of Series

       ' Create a Worksheet Object
       Set objXLsheet = CreateObject("Excel.Sheet")

       Randomize Timer
       ' Insert Random data into Cells for the two Series:
       For iRow = 1 To cNumRows
          For iCol = 1 To cNumCols
             objXLsheet.Cells(iRow, iCol).Value = Int(Rnd * 50) + 1
          Next iCol
       Next iRow

       ' Insert Named Ranges:
       For iRow = 1 To cNumRows
       ' Enter the following two lines as one, single line of code:
       strTmpRange = "R" & iRow & "C" & Format$(1) & ":R" & iRow & "C"
          & Format$(cNumCols)
       ' Enter the following two lines as one, single line of code:
       objXLsheet.Parent.Names.Add "Range" & Format$(iRow), "=Sheet1!" &
          strTmpRange
       Next iRow

       ' Add a ChartObject to the worksheet:
       Set objChart1 = objXLsheet.ChartObjects.Add(100, 100, 200, 200)

       ' Assign the Ranges created above as the individual series
       ' for the chart:
       For iRow = 1 To cNumRows
          objChart1.Chart.SeriesCollection.Add "Range" & Format$(iRow)
       Next iRow

       ' Copy Chart for paste into the Word Report:
       objChart1.Copy

       ' Close this instance of Excel without saving changes:
       objXLsheet.Parent.Saved = True
       objXLsheet.application.Quit

   End Sub

12. Press the F5 key to run the program.

After the Excel chart is created in the background, Word starts and the chart is inserted into the document where the bookmark Chart1 was established. Then it is saved to disk.

Keywords          : kbprg IAPOLE vbwin 
Version           : WINDOWS:3.0
Platform          : WINDOWS
Issue type        : kbhowto

Last Reviewed: September 30, 1997