Controlling Word from Excel

The example in this section demonstrates an Automation session by using Word. The MakeMemos procedure creates three customized memos in Word and then saves each document to a file. The information used to create the memos is stored in a worksheet, as shown in Figure 20-6.

EE

^V^tnl

xi

A

C

E

F

1

Regionl

145

(134,555

2

Region2

122

(127,003

Create Word Memos Using

3

Region3

203

(288,323

Data on This Worksheet

4

5

G

Message:

The monthly sales data for your region is listed below. This information was obtained from the central database. Please call if you have any questions._

7

À

1 H < ► ^Sheetl /

i<i

Figure 20-6: Word automatically generates three memos based on this Excel data.

Figure 20-6: Word automatically generates three memos based on this Excel data.

The MakeMemos procedure, presented in Listing 20-3, starts by creating an object called WordApp. The routine cycles through the three rows of data in Sheetl and uses Word's properties and methods to create each memo and save it to disk. A range named Message (in cell E6) contains the text used in the memo. All the action occurs behind the scenes: That is, Word is not visible.

Listing 20-3: Generating Word Documents from an Excel VBA Program

Sub MakeMemos()

' Creates memos in word using Automation Dim WordApp As Object Dim Data As Range, message As String Dim Records As Integer, i As Integer

Dim Region As String, SalesAmt As String, SalesNum As String Dim SaveAsName As String

' Start Word and create an object (late binding) Set WordApp = CreateObject("Word.Application")

' Information from worksheet

Set Data = Sheets("Sheet1").Range("A1") Message = Sheets("Sheet1").Range("Message")

Listing 20-3 (Continued)

' Cycle through all records in Sheetl

Records = Application.CountA(Sheets("Sheet1").Range("A:A")) For i = 1 To Records

Update status bar progress message Application.StatusBar = "Processing Record " & i

Assign current data to variables

Region = Data.Cells(i, 1).Value

SalesNum = Data.Cells(i, 2).Value

SalesAmt = Format(Data.Cells(i, 3).Value, "#,000")

Determine the file name

SaveAsName = ThisWorkbook.Path & "\" & Region & ".doc"

Send commands to Word With WordApp

.Documents.Add With .Selection

.Font.Bold = True

.ParagraphFormat.Alignment = 1

.TypeParagraph

.TypeParagraph

.ParagraphFormat.Alignment = 0

.Font.Bold = False

.TypeText Text:="Date:" & vbTab & _

Format(Date, "mmmm d, yyyy") .TypeParagraph

.TypeText Text:="To:" & vbTab & Region & _ " Manager" .TypeParagraph

.TypeText Text:="From:" & vbTab & _

Application.UserName .TypeParagraph .TypeParagraph .TypeText Message .TypeParagraph .TypeParagraph

.TypeText Text:="Units Sold:" & vbTab & _ SalesNum

.TypeParagraph

.TypeText Text:="Amount:" & vbTab & _ FormatCSalesAmt, "$#,##0") End With

.ActiveDocument.SaveAs FileName:=SaveAsName

End With Next i

' Kill the object WordApp.Quit Set WordApp = Nothing

' Reset status bar

Application.StatusBar = ""

MsgBox Records & " memos were created and saved in " & ThisWorkbook.Path End Sub

Figure 20-7 shows a document created by the MakeMemos procedure.

Ü Region1.doc

Microsoft Word

; File Edit V

ew hiseit Format lools Table Window Help Type a question for help

X

hüLid iJ J A

- # M Read

I

; 44 Normal

Times New Roman »

i2 - B I u \\m\w m s iß- |E iE tF iF ^- A -

=

Lg------

. 1 2 .

3 4 5

M E M O R A N D U M

d

Date: August 25, 2003 To: B.egion.1 Manager From: John Walkenbach

The monthly sales data for your: from the central database. Please

region is listed below. This information was obtained : call if you have any questions.

Units Sold: Amount:

$134,555

1 •

J

_ ■

Page 1 Sec 1

1/1 At 3.1

Ln 11 Col 1 English (U.S

_

Figure 20-7: An Excel procedure created this Word document.

Figure 20-7: An Excel procedure created this Word document.

This workbook is available on the companion CD-ROM. However, do not run this procedure directly from the CD-ROM.The code attempts to write the files to the same directory as the Excel workbook.

Creating this macro involved several steps. I started by recording a macro in Word. I recorded my actions while creating a new document, adding and formatting some text, and saving the file. That Word macro provided the information that I needed about the appropriate properties and methods. I then copied the macro to an Excel module. Notice that I used With-End With. I added a dot before each instruction between With and End With. For example, the original Word macro contained (among others) the following instruction:

Documents.Add

I modified the macro as follows:

With WordApp

.Documents.Add ' more instructions here End With

The macro that I recorded in Word used a few of Word's built-in constants. Because this example uses late binding, I had to substitute actual values for those constants. I was able to learn the values by using the Immediate window in Word's VBE.

0 0

Post a comment