The Excel® Analyst's Guide to Access®

Chapter 16. Exploring Excel and Access Automation

In the last few chapters, you have learned several ways to automate your analytical processes to achieve higher productivity, controlled analysis, and reproducibility. In this chapter, automation takes on different meaning. Automation here will define the means of manipulating or controlling one application with another. Why would you even want to control one application with another? Think about all the times you have crunched data in Access only to bring the results into Excel for presentation and distribution. Think about all the times you have sent Excel data to Access only to open Access and run a set of queries or output a report.

The reality is that each of these applications has its strengths, which you routinely leverage through manual processes. So why not automate these processes? The goal of this chapter is to give you a solid understanding of how to use automation to control Excel from Access and vice versa.

Understanding the Concept of Binding

Each program in the Microsoft Office Suite comes with its own Object Library. As you know, the Object Library is a kind of encyclopedia of all the objects, methods, and properties available in each Office application. Excel has its own Object Library, just as Access has its own Object Library, just as all the other Office applications have their own Object Library. In order for Excel to be able to speak to another Office program such as Access, you have to bind it to that program. ...