1. Chapter 1 Introduction to Access/Excel Integration

   1. Communications Between Excel and Access

   2. Automation Objects

   3. ADO and DAO

   4. Tackling Projects

   5. Designing Applications

   6. Next Steps

2. Chapter 2 Using the Excel User Interface

   1. Using External Data

   2. Using Database Queries

   3. Returning a PivotTable to Excel

   4. Using Microsoft QueryMicrosoft Query to Gather Data

   5. Keeping the Query Updated with VBA

   6. Next Steps

3. Chapter 3 Data Access from Excel VBA

   1. Writing a Reusable Module for Data Access

   2. Choosing Between ADO and DAO

   3. CopyFromRecordset Versus Looping

   4. Formatting Techniques

   5. Formatting Techniques Example

   6. Summary

4. Chapter 4 Integration from the Access Interface

   1. Importing Excel Data

   2. Linking Excel Data

   3. Using Export and Analyzing It with Microsoft Office Excel

   4. Using Raw Exported Access Data in Excel

   5. Exporting an Access Report to Excel

   6. Next Steps

5. Chapter 5 Using Access VBA to Automate Excel

   1. High-Level Excel Objects

   2. Other Excel Objects

   3. Writing and Using Queries in VBA

   4. Referencing Sheets, Ranges, and Cells

   5. Writing Excel Worksheet Functions from Access VBA

   6. Chapter Summary and Next Steps

6. Chapter 6 Using Excel Charts and Pivot Tables with Access Data

   1. Automating Pivot Tables

   2. Building a Regular Chart

   3. Using an Array Formula

   4. Graphing Variables in a Model

7. Chapter 7 Leveraging SQL Server Data with Microsoft Office

   1. Pass-Through Queries Versus Linked Tables

   2. Creating a Connection Using VBA

   3. Building the Connection in Access

   4. Pulling Data in with Excel Alone

   5. Using DTS to Automate Excel

   6. Crosstab Queries on SQL Server

   7. SQL Server Summary

8. Chapter 8 Advanced Excel Reporting Techniques

   1. Writing Flexible Formulas

   2. Changing Data in an Existing Report

   3. Creating a Report from Scratch

   4. Using an Access Table for Reporting

   5. Putting It Together

9. Chapter 9 Using Access and Excel Data in Other Applications

   1. Automating Microsoft Word

   2. Getting Information from Microsoft Word

   3. Automating PowerPoint

   4. Using Data in MapPoint

   5. Summary

10. Chapter 10 Creating Form Functionality in Excel

    1. Working with the UserForm

    2. Accepting Parameters

    3. Other Useful Items

    4. Next Steps

11. Chapter 11 Building Graphical User Interfaces

    1. Setting Up a Form

    2. Using Events

    3. Using Data

    4. Adding Buttons

    5. Tab Order

    6. Next Steps

12. Chapter 12 Tackling an Integration Project

    1. The Project Description

    2. Main Menu

    3. Customer Information

    4. Billing and Payment Information

    5. Contact History

    6. Services and Charges

    7. Expense Entry

    8. Invoices

    9. Letters

    10. Income Statements

    11. Project Summary

1. Appendix A Excel Object Model

   1. Application Object

   2. Workbook and Worksheet Objects

   3. Excel Object Model Summary

2. Appendix B VBA Basics

   1. Dim and Set Statements

   2. Loops

   3. With Statement

   4. Goto Statement

   5. Select Case Statement

   6. If Statements

   7. VBA Summary

3. Colophon

About the Author Michael Schmalz works in the financial services industry and also provides consulting services to a variety of industries. He specializes in Microsoft products, particularly the Microsoft Office Suite. Michael graduated with a B.S. in finance from Penn State and lives with his family in Pennsylvania.

Colophon Our look is the result of reader comments, our own experimentation, and feedback from distribution channels. Distinctive covers complement our distinctive approach to technical topics, breathing personality and life into potentially dry subjects.

The animals on the cover of Integrating Excel and Access are common partridges (perdix cinerea or perdix perdix), one of several species known collectively as the gray partridge. A non-migratory game bird native to Europe, the gray partridge was introduced to North America when its numbers in Europe began to decline, and it is now common in the northern United States and southern Canada. The decline of the gray partridge in Europe is thought to be due to changes in European agricultural practices, such as the use of herbicides, rather than to overenthusiastic hunters.

The gray partridge is a round, plump bird usually between a foot and a foot and a half long. The male has a mottled plumage of gray and brown, highlighted by a cinnamon-red face and throat and a distinctive horeshoe-shaped, chestnut-colored mark on his belly. The female looks similar but is duller in color, and her horseshoe patch may be lighter or smaller than the male's, or it may not show up at all. Once known simply as "the partridge," their name changed when the red-legged partridge became common-the "gray" was then added due to the color of their legs.

Gray partridges live mainly on farmland and feed on grass and seeds, although chicks eat insects for the first few weeks of life. Their breeding season lasts from mid-April to early September, when the female may lay up to 20 eggs in her nest, also known as a clutch, which is usually hidden in a depression in the ground at the base of a hedge or a group of plants. The eggs hatch after almost a month, and both parents tend the chicks together. After the breeding season, they form larger groups called coveys.

Despite the impression given in the holiday song "The Twelve Days of Christmas," gray partridges generally fly close to the ground and do not nest in trees. However, the male red-legged partridge apparently sat in pear trees and was commonly known in folklore to be lascivious, not unlike the way we think of rabbits today. Pear trees were involved in traditional celebrations of Twelfth Night, including wassailing of fruit trees and even fertility rituals, in which a maiden circled a pear tree backward to reveal her future husband's face within its branches. Perhaps these associations eventually helped "the partridge in the pear tree" work his way into the song.

Reba Libby was the production editor and copyeditor for Integrating Excel and Access. Ann Atalla proofread the book. Colleen Gorman and Claire Cloutier provided quality control. John Bickelhaupt wrote the index.

Karen Montgomery designed the cover of this book, based on a series design by Edie Freedman. The cover image is a 19th-century engraving from Cassel's Natural History. Karen Montgomery also produced the cover layout with Adobe InDesign CS using Adobe's ITC Garamond font.

David Futato designed the interior layout. This book was converted by Keith Fahlgren to FrameMaker 5.5.6 with a format conversion tool created by Erik Ray, Jason McIntosh, Neil Walls, and Mike Sierra that uses Perl and XML technologies. The text font is Linotype Birka; the heading font is Adobe Myriad Condensed; and the code font is LucasFont's TheSans Mono Condensed. The illustrations that appear in the book were produced by Robert Romano, Jessamyn Read, and Lesley Borash using Macromedia FreeHand MX and Adobe Photoshop CS. The tip and warning icons were drawn by Christopher Bing. This colophon was written by Reba Libby.The production editors for Book Title, eMatter Edition were Ellie Cutler and Jeff Liggett. Linda Walsh was the product manager. Kathleen Wilson provided design support. Lenny Muellner, Mike Sierra, Erik Ray, and Benn Salter provided technical support. This eMatter Edition was produced with FrameMaker 5.5.6.