Controlling Excel from Word
As you might expect, you can also control Excel from another application (such as another programming language or a Word VBA procedure). For example, you might want to perform some calculations in Excel and return the result to a Word document.
You can create any of the following Excel objects with the adjacent functions:
^ Application object: CreateObject("Excel.Application") ^ Workbook object: CreateObject("Excel.Sheet") ^ Chart object: CreateObject("Excel.Chart")
Figure 23-3:
An Excel VBA procedure created this Word document.
Figure 23-3:
An Excel VBA procedure created this Word document.
The example described in this section is a Word macro that creates an Excel Workbook object (whose moniker is Excel.Sheet) from an existing workbook named projections.xls. The macro prompts the user for two values and then creates a data table and chart, which are stored in the Word document.
The initial workbook is shown in Figure 23-4. The MakeExcelChart procedure prompts the user for two values and inserts the values into the worksheet.
Figure 23-4:
A VBA procedure in Word uses this worksheet.
Figure 23-4:
A VBA procedure in Word uses this worksheet.
Recalculating the worksheet updates a chart. The data and the chart are then copied from the Excel object and pasted into a new document. The results are shown in Figure 23-5.
Figure 23-5:
The Word VBA procedure uses Excel to create this document.
Figure 23-5:
The Word VBA procedure uses Excel to create this document.
The code for the MakeExcelChart procedure follows:
Sub MakeExcelChart()
Dim XLSheet As Object Dim StartVal, PctChange Dim Wbook As String
' Create a new document Documents.Add
' Prompt for values
StartVal = InputBox("Starting Value?") PctChange = InputBox("Percent Change?")
' Create Sheet object
Wbook = ThisDocument.Path & "\projections.xls"
Set XLSheet = GetObject(Wbook, "Excel.Sheet").ActiveSheet
' Put values in sheet
XLSheet.Range("StartingValue") = StartVal XLSheet.Range("PctChange") = PctChange XLSheet.Calculate
Insert page heading Selection.Font.Size = 14 Selection.Font.Bold = True Selection.TypeText "Monthly Format(PctChange, "0.0%") Selection.TypeParagraph Selection.TypeParagraph
Copy data from sheet i
XLSheet.Range("data")
Selection.Paste paste to document Copy
Copy chart and paste to document XLSheet.ChartObjects(1).Copy Selection.PasteSpecial _ Link:=False, _
DataType:=wdPasteMetafilePicture, _ Placement:=wdInLine, DisplayAsIcon:=False
Kill the object
Set XLSheet = Nothing
This example is available at the book's Web site.
Post a comment