Page 491 - Microsoft Office Excel 2003 Programming Inside Out

Excel and Other Office Applications

PctChange = InputBox("Percent Change?”)
WBook = "C:\GSC\Financial\projections.xls"
Set XLSheet = GetObject(WBook, "Excel.Sheet”).ActiveSheet

XLSheet.Range("StartingValue”) = StartVal
XLSheet.Range("PctChange”) = PctChange
XLSheet.Calculate
With Selection
.Font.Size = 14
.Font.Bold = True
.TypeText "Monthly Increment: " & Format(PctChange, "0.0%”)
.TypeParagraph
.TypeParagraph
End With
XLSheet.Range("data”).Copy
Selection.Paste

XLSheet.ChartObjects(1).Copy
Selection.PasteSpecial link:=False, DataType:=wdPasteMetafilePicture, _
Placement:=wdInLine, DisplayAsIcon:=False Chapter 21
Set XLSheet = Nothing
End Sub

Working with Multiple Applications to Get the
Job Done

There are times when simply working within Excel is too restrictive. That’s the main reason
you bind your workbook to additional applications.
Throughout this chapter, you learned how to access different applications. The Shell function
can be used to open programs that fall outside of Microsoft Office Suite. However, when
working within the Microsoft Office Suite you need to access the different Application objects.
It’s possible to reference a new Application object using late binding; however, it’s recom­
mended that you use early binding when programming your procedures.
To automate the objects in another application, you create an object variable referring to the
target application or an object in the application. You can use early binding or late binding to
establish the link between VBA and the other application’s objects. Early binding requires
that you establish a reference to the target application’s object library, and you must declare
any object variables that refer to the target objects using their correct type. If you declare the
object variables as the generic Object type, VBA uses late binding.
Early binding produces code that executes faster than late binding, and you can get informa­
tion on the target application’s objects using the Object Browser and the shortcut tips that

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