Skip to Main Content
Integrating Excel and Access
book

Integrating Excel and Access

by Michael Schmalz
November 2005
Intermediate to advanced content levelIntermediate to advanced
236 pages
6h 32m
English
O'Reilly Media, Inc.
Content preview from 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 ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Business solutions Automating Microsoft® Access with VBA

Business solutions Automating Microsoft® Access with VBA

Susan Sales Harkins, Mike Gunderloy
Microsoft® Office Excel 2003 Programming Inside Out

Microsoft® Office Excel 2003 Programming Inside Out

Curtis Frye, Wayne S. Freeze, Felicia K. Buckingham

Publisher Resources

ISBN: 0596009739Supplemental ContentErrata Page