Controlling Excel from another application

You can, of course, 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")

Listing 20-4 shows a procedure that is located in a VBA module in a Word 2003 document. This procedure creates an Excel Worksheet object —whose moniker is "Excel.Sheet" — from an existing workbook.

Listing 20-4: Producing an Excel Worksheet on a Word Document

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 Increment: " & _

Format(PctChange, "0.0%") Selection.TypeParagraph Selection.TypeParagraph

' Copy data from sheet & paste to document XLSheet.Range("data").Copy Selection.Paste

' 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 End Sub

The initial workbook is shown in Figure 20-8. The MakeExcelChart procedure prompts the user for two values and inserts the values into the 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 20-9.

Figure 20-8: A VBA procedure in Word uses this worksheet.
Figure 20-9: The Word VBA procedure uses Excel to create this document.
0 0

Post a comment