How To Create a Report in Word w/Excel Chart via OLE Automation

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:

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.