Page 484 - Microsoft Office Excel 2003 Programming Inside Out

Microsoft Office Excel 2003 Programming Inside Out

Sub CopyFromExcelToWord()
Dim wdApp As Word.Application
ThisWorkbook.Sheets("Table”).Range("A1:B6”).Copy
Set wdApp = New Word.Application
With wdApp.Application
.Documents.Open Filename:="C:\test.doc"
With .Selection
.EndKey unit:=wdStory
.TypeParagraph
.Paste
End With
.ActiveDocument.Save
.Quit
End With
Set wdApp = Nothing
End Sub

Tip Debugging with Hidden Application Objects
When a procedure includes applications that aren’t visible, you might run into memory
issues when debugging your code. When a procedure opens a hidden copy of an application
and the code is stopped before the Quit method has been executed, an instance of the
Chapter 21
application will continue to run in the background until it has been forced to close. Each
time the procedure is run and stopped before the Quit method is executed, an additional
copy of the application will be in memory. This can cause serious memory errors with your
system, so be sure to force the hidden application to close by using the Task Manager.

The New keyword creates a new instance of Word, even if it is already open. The Open
method of the Documents collection is used to open an existing file. The code then selects the
end of the document, enters a new empty paragraph, and pastes the range. The document is
then saved, and the new instance of Word is closed.

Now that you can open an existing Word document and place Excel data into Word, consider
the following scenario. The Garden Supply Company has the promotional schedule outlined
in a document named Spring Promotion.doc. You need to enter the same information into
Excel. The following procedure will open the Word document and will place a copy of the file
contents into a new Excel workbook:
Sub CopyWordToExcel()
Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document
Dim tString As String
Dim tRange As Word.Range
DimiAsLong
DimrAsLong

458
Part 6: Excel and the Outside World: Collaborating Made Easy