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
- 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.
- Add a command button (Command1) to Form1.
- 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.
- 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
- 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
- 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
|