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

Last reviewed: September 29, 1997
Article 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          : IAPOLE vbwin GnrlVb kbprg
Technology        : kbole kbvba
Version           : WINDOWS:3.0
Platform          : WINDOWS
Issue type        : kbhowto


================================================================================


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: September 29, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.