Controlling Excel from Word

Vertex42 The Excel Nexus

Professional Excel Templates

Get Instant Access

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.

Was this article helpful?

0 -1

Post a comment