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.

Vba Controlling

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.

0 -1

Post a comment