Microsoft KB Archive/114378

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

Article ID: 114378

Article Last Modified on 6/29/2004

-

APPLIES TO


 * Microsoft Visual Basic 3.0 Professional Edition
 * Microsoft Visual Basic 3.0 Professional Edition

-



This article was previously published under Q114378



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:

112417 How To Create Excel Chart w/ OLE Automation from Visual Basic

Step-by-Step Example
 Start Microsoft Word version 6.0 for Windows. From the File menu, choose New (ALT, F, N). Then select Template. Insert the text that you want to appear in all reports. Then place the cursor where you want the Excel Chart to be inserted. From the Edit menu, choose BookMark (ALT, E, B). Name the bookmark Chart1, and click the Add button. Save the Template. From the File menu, choose Save (ALT F, S), giving the filename of the template "C:\WINWORD\TEMPLATE\MYREPORT.DOT" Close Word. From the File Menu, choose Exit (ALT, F, X). Start a new project in Visual Basic (ALT, F, N). Form1 is created by default.</li> Add a command button (Command1) to Form1.</li>  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. </li>  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 </li>  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 </li> Press the F5 key to run the program.</li></ol>

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: kbhowto kbprogramming KB114378

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.