Writing Excel Macros with VBA, 2nd Edition

Appendix E. Programming Excelfrom Another Application

In this appendix, we will briefly describe how the Excel object model can be programmed from within certain other applications, including Microsoft Access, Word, and PowerPoint.

Briefly put, a well-known technique that Microsoft refers to as Automation (formerly called OLE Automation) allows one application to gain access to the objects of another. An application that “exposes” its objects is called an Automation server. An application that can access the objects of an Automation server is called an Automation controller or Automation client. Since Microsoft Word, Access, Excel, and PowerPoint are Automation servers and Automation controllers, as a VBA programmer, you can program any of these applications from within any other.

Setting a Reference to the Excel Object Model

The first step in communicating with the Excel object model is to set a reference to its object library. Each of the client applications (Word, Access, Excel, and PowerPoint) has a References menu item under the Tools menu. Selecting this item displays the References dialog box shown in Figure E-1. From here, we can select the object models that we want to access from within the Automation controller.

Figure E-1. The References dialog box

Getting a Reference to the Excel Application Object

Once the proper references are set, we can declare an object variable ...