Page 483 - Microsoft Office Excel 2003 Programming Inside Out

Excel and Other Office Applications

Dim wbkSheet As Excel.Workbook
Set wbkSheet = CreateObject("Excel.Sheet”)
To automate Excel starting with the Workbook object that contains a worksheet with a chart
and another worksheet, use the following code.

Dim wbkChart As Excel.Workbook
Set wbkChart = CreateObject("Excel.Chart”)
When automating Excel starting from a Workbook object or automating Word starting from
a Document object, an implicit reference is created to the Application object. If you need to
access properties and methods of the Application object, use the appropriate Application
property of the Document or Workbook objects. Using the Document or Workbook objects as
top-level objects reduces the amount of code you have to write. In most cases your code will
be easier to follow and more consistent than when you reference the Application object. Table
21-1 lists all the top-level Office objects that can be referenced and their class names.

Table 21-1. Top-Level Office Objects and Their Associated Class Names
Chapter 21
Top-Level Office Object Class Name
Access Application object Access.Application
Excel Application object Excel.Application
Excel Workbook object Excel.Sheet
Excel.Chart
FrontPage Application object FrontPage.Application
Outlook Application object Outlook.Application
PowerPoint Application object PowerPoint.Application
Word Application object Word.Application
Word Document object Word.Document

Opening a Document in Word

If you want to open a file created using a different Office application than you are currently
using, use the GetObject function to directly open the file. However, it’s just as easy to open an
instance of the application and open the file from the application.
The following code copies a range in Excel to the clipboard. It then starts a new instance of
Word, opens an existing Word document and pastes the range at the end of the document.
Because the code uses early binding, be sure to set up the reference to the Word Object
Library first.

Note In the example that follows, be sure to replace the worksheet name and the file-
name to reference a file located on your computer. If you are referencing files that don’t
exist, an error will occur when you test this procedure.

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