Table of Contents

Integrating Excel and Access
By Michael Schmalz
...............................................
Publisher: O'Reilly
Pub Date: November 2005
ISBN: 0-596-00973-9
Pages: 232

Table of Contents  | Index

---

		Copyright
		Preface
			Who Should Read This Book
			What's in This Book
			Conventions in This Book
			References in VBA
			We'd Like Your Feedback!
			Safari Enabled
			Acknowledgments
		Chapter 1.  Introduction to Access/Excel Integration
			Section 1.1.  Communications Between Excel and Access
			Section 1.2.  Automation Objects
			Section 1.3.  ADO and DAO
			Section 1.4.  Tackling Projects
			Section 1.5.  Designing Applications
			Section 1.6.  Next Steps
		Chapter 2.  Using the Excel User Interface
			Section 2.1.  Using External Data
			Section 2.2.  Using Database Queries
			Section 2.3.  Returning a PivotTable to Excel
			Section 2.4.  Using Microsoft Query to Gather Data
			Section 2.5.  Keeping the Query Updated with VBA
			Section 2.6.  Next Steps
		Chapter 3.  Data Access from Excel VBA
			Section 3.1.  Writing a Reusable Module for Data Access
			Section 3.2.  Choosing Between ADO and DAO
			Section 3.3.  CopyFromRecordset Versus Looping
			Section 3.4.  Formatting Techniques
			Section 3.5.  Formatting Techniques Example
			Section 3.6.  Summary
		Chapter 4.  Integration from the Access Interface
			Section 4.1.  Importing Excel Data
			Section 4.2.  Linking Excel Data
			Section 4.3.  Using Export and Analyzing It with Microsoft Office Excel
			Section 4.4.  Using Raw Exported Access Data in Excel
			Section 4.5.  Exporting an Access Report to Excel
			Section 4.6.  Next Steps
		Chapter 5.  Using Access VBA to Automate Excel
			Section 5.1.  High-Level Excel Objects
			Section 5.2.  Other Excel Objects
			Section 5.3.  Writing and Using Queries in VBA
			Section 5.4.  Referencing Sheets, Ranges, and Cells
			Section 5.5.  Writing Excel Worksheet Functions from Access VBA
			Section 5.6.  Chapter Summary and Next Steps
		Chapter 6.  Using Excel Charts and Pivot Tables with Access Data
			Section 6.1.  Automating Pivot Tables
			Section 6.2.  Building a Regular Chart
			Section 6.3.  Using an Array Formula
			Section 6.4.  Graphing Variables in a Model
		Chapter 7.  Leveraging SQL Server Data with Microsoft Office
			Section 7.1.  Pass-Through Queries Versus Linked Tables
			Section 7.2.  Creating a Connection Using VBA
			Section 7.3.  Building the Connection in Access
			Section 7.4.  Pulling Data in with Excel Alone
			Section 7.5.  Using DTS to Automate Excel
			Section 7.6.  Crosstab Queries on SQL Server
			Section 7.7.  SQL Server Summary
		Chapter 8.  Advanced Excel Reporting Techniques
			Section 8.1.  Writing Flexible Formulas
			Section 8.2.  Changing Data in an Existing Report
			Section 8.3.  Creating a Report from Scratch
			Section 8.4.  Using an Access Table for Reporting
			Section 8.5.  Putting It Together
		Chapter 9.  Using Access and Excel Data in Other Applications
			Section 9.1.  Automating Microsoft Word
			Section 9.2.  Getting Information from Microsoft Word
			Section 9.3.  Automating PowerPoint
			Section 9.4.  Using Data in MapPoint
			Section 9.5.  Summary
		Chapter 10.  Creating Form Functionality in Excel
			Section 10.1.  Working with the UserForm
			Section 10.2.  Accepting Parameters
			Section 10.3.  Other Useful Items
			Section 10.4.  Next Steps
		Chapter 11.  Building Graphical User Interfaces
			Section 11.1.  Setting Up a Form
			Section 11.2.  Using Events
			Section 11.3.  Using Data
			Section 11.4.  Adding Buttons
			Section 11.5.  Tab Order
			Section 11.6.  Next Steps
		Chapter 12.  Tackling an Integration Project
			Section 12.1.  The Project Description
			Section 12.2.  Main Menu
			Section 12.3.  Customer Information
			Section 12.4.  Billing and Payment Information
			Section 12.5.  Contact History
			Section 12.6.  Services and Charges
			Section 12.7.  Expense Entry
			Section 12.8.  Invoices
			Section 12.9.  Letters
			Section 12.10.  Income Statements
			Section 12.11.  Project Summary
		Appendix A.  Excel Object Model
			Section A.1.  Application Object
			Section A.2.  Workbook and Worksheet Objects
			Section A.3.  Excel Object Model Summary
		Appendix B.  VBA Basics
			Section B.1.  Dim and Set Statements
			Section B.2.  Loops
			Section B.3.  With Statement
			Section B.4.  Goto Statement
			Section B.5.  Select Case Statement
			Section B.6.  If Statements
			Section B.7.  VBA Summary
		About the Author
		Colophon
		Index