Integrating Excel and Access

Chapter 5. Using Access VBA to Automate Excel

While the earlier pieces of the book lay a good foundation, and the rest of the book certainly builds on it, if you were only going to focus on one chapter, this should be it. Access is a great application for building a front end. While Access provides some nice wizards for many of the functions that you can perform, this chapter focuses on VBA code that you write from scratch. If you have done any work in Visual Basic and have built forms, the form building tools in Access will be very familiar to you. The nice thing about using VBA is that the syntax is the same from application to application; the main difference is the objects that you are using.

This chapter is broken up into several distinct sections. The first part focuses on creating an Excel Workbook that can be used with Access VBA . The next part looks at some Access features with queries to create the data used for reporting, including using VBA to write SQL. The rest of the chapter focuses on how to use other Excel capabilities from Access.

High-Level Excel Objects

There are three primary objects that your Access VBA needs creating in order to automate Excel: the Excel Application object, the Excel Workbook object, and the Excel Worksheet object. These objects are created in that order.

In order to use the Excel objects , you need to create a reference to Excel from Access VBA. When you are in an Access database, click on the Modules table and then press the New button. This ...