Page 482 - Microsoft Office Excel 2003 Programming Inside Out

Microsoft Office Excel 2003 Programming Inside Out

Set wdApp = New Word.Application
Set docNew = wdApp.Documents.Add
wdApp.Selection.TypeText "This file was created " & Date
With docNew
MsgBox "‘" & .Name & "‘ was created " & Date & "."
.Close wdDoNotSaveChanges
End With
wdApp.Quit
Set wdApp = Nothing
End Sub
Typically, you’ll create an object variable that refers to the Application object representing the
application you want to access through automation. When the Application object has been
referenced, you can include additional references to the object’s child objects to navigate to the
object or method you want to manipulate. Use the Set statement to assign object variables to
child objects.

Note The top-level object is the Application object. The Application object contains other
objects that you can only access if the Application object exists. The objects dependent on
the Application objects, are often referred to as the child objects. The child objects may also
have children of their own. For example, the Excel Workbook object is the child object to
the Excel Application object, and the Worksheets object is the child object of the Excel
Chapter 21
Workbook object.
However, Microsoft Excel and Word make it possible to create a top-level reference to some
child objects of the Application objects. Knowing this fact, you can rewrite the code for the
CreateWordDocumentFromExcel procedure to start from a reference to a Word Document
object.

Sub CreateWordDocumentFromExcel2()
Dim docNew As Word.Document
Set docNew = New Word.Document
Set docNew = Documents.Add
docNew.Application.Selection.TypeText "This file was created " & Date
With docNew
MsgBox "‘" & .Name & "‘ was created " & Date & "."
.Close wdDoNotSaveChanges
End With
Set docNew = Nothing
End Sub

You can use the same theory in Excel using the Workbook object as your top-level reference.
You can do this by using the Excel.Sheet class name to create the workbook or by using the
Excel.Chart class name to create a workbook that contains a worksheet with an embedded
Chart object and a worksheet that contains a default data set for the chart.
To create a Workbook object, you use the CreateObject function because the Excel.Sheet and
Excel.Chart class names don’t support the New keyword. The following example automates
Excel, starting with a Workbook object that contains a single worksheet:
456
Part 6: Excel and the Outside World: Collaborating Made Easy