Chapter 12. Automation

No Access application exists in isolation. Because Windows is a multitasking operating system, you will often want to be able to link Access with other Windows applications. Windows provides two mechanisms for communicating between applications: Object Linking and Embedding (OLE), which has been renamed ActiveX, and Dynamic Data Exchange (DDE), an older technology that is supported primarily for backward compatibility. ActiveX is easy for users and application programmers to work with and allows for the creation of custom controls. It also accommodates Automation, making it possible for Access to control various applications using VBA.

This chapter presents examples of using Automation with several Microsoft Office products. You'll also find an example of using DDE to perform a task with the Windows shell. You'll learn to activate an embedded ActiveX object (a sound file), and you'll learn how to control Access itself via Automation. You'll see how to use the statistical, analytical, and financial prowess of the Excel function libraries directly from Access, as well as how to retrieve Word Summary Info for any selected document. Then you'll dig into Automation, creating a form that allows you to alter properties of Microsoft Graph objects on the form. Finally, you'll delve into PowerPoint, which in previous incarnations didn't support Automation, and you'll see an example of automating tasks in Outlook. These examples will show how you can manipulate and create objects in these applications directly from Access.

Almost all of the examples in this chapter ask you to set a reference within VBA, using the Tools References menu item. Because this book supports multiple versions of Office, we've selected the Office 11 type libraries in each example. You'll need to modify the instructions to set a reference to the correct type library in each case, based on the version of Office you have installed. Besides these version numbering differences, all the examples should behave the same, no matter which version of Office you're using.