Integrating Excel and Access

492 Pages·2006·5.11 MB·English

by  Michael Schmalz

Integrating Excel and Access
Table of Contents
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
1.1. Communications Between Excel and Access
1.2. Automation Objects
1.3. ADO and DAO
1.4. Tackling Projects
1.5. Designing Applications
1.6. Next Steps
Chapter 2. Using the Excel User Interface
2.1. Using External Data
2.2. Using Database Queries
2.3. Returning a PivotTable to Excel
Figure 2-13. Microsoft Query screen
2.5. Keeping the Query Updated with VBA
2.6. Next Steps
Chapter 3. Data Access from Excel VBA
3.1. Writing a Reusable Module for Data Access
3.2. Choosing Between ADO and DAO
3.3. CopyFromRecordset Versus Looping
3.4. Formatting Techniques
3.5. Formatting Techniques Example
3.6. Summary
Chapter 4. Integration from the Access Interface
4.1. Importing Excel Data
4.2. Linking Excel Data
4.3. Using Export and Analyzing It with Microsoft Office Excel
4.4. Using Raw Exported Access Data in Excel
4.5. Exporting an Access Report to Excel
4.6. Next Steps
Chapter 5. Using Access VBA to Automate Excel
5.1. High-Level Excel Objects
5.2. Other Excel Objects
5.3. Writing and Using Queries in VBA
5.4. Referencing Sheets, Ranges, and Cells
5.5. Writing Excel Worksheet Functions from Access VBA
5.6. Chapter Summary and Next Steps
Chapter 6. Using Excel Charts and Pivot Tables with Access Data
6.1. Automating Pivot Tables
6.2. Building a Regular Chart
6.3. Using an Array Formula
6.4. Graphing Variables in a Model
Chapter 7. Leveraging SQL Server Data with Microsoft Office
7.1. Pass-Through Queries Versus Linked Tables
7.2. Creating a Connection Using VBA
7.3. Building the Connection in Access
7.4. Pulling Data in with Excel Alone
7.5. Using DTS to Automate Excel
7.6. Crosstab Queries on SQL Server
7.7. SQL Server Summary
Chapter 8. Advanced Excel Reporting Techniques
8.1. Writing Flexible Formulas
8.2. Changing Data in an Existing Report
8.3. Creating a Report from Scratch
8.4. Using an Access Table for Reporting
8.5. Putting It Together
Chapter 9. Using Access and Excel Data in Other Applications
9.1. Automating Microsoft Word
9.2. Getting Information from Microsoft Word
9.3. Automating PowerPoint
9.4. Using Data in MapPoint
9.5. Summary
Chapter 10. Creating Form Functionality in Excel
10.1. Working with the UserForm
10.2. Accepting Parameters
10.3. Other Useful Items
10.4. Next Steps
Chapter 11. Building Graphical User Interfaces
11.1. Setting Up a Form
11.2. Using Events
11.3. Using Data
11.4. Adding Buttons
11.5. Tab Order
11.6. Next Steps
Chapter 12. Tackling an Integration Project
12.1. The Project Description
12.2. Main Menu
12.3. Customer Information
12.4. Billing and Payment Information
12.5. Contact History
12.6. Services and Charges
12.7. Expense Entry
12.8. Invoices
12.9. Letters
12.10. Income Statements
12.11. Project Summary
Appendix A. Excel Object Model
A.1. Application Object
A.2. Workbook and Worksheet Objects
A.3. Excel Object Model Summary
Appendix B. VBA Basics
B.1. Dim and Set Statements
B.2. Loops
B.3. With Statement
B.4. Goto Statement
B.5. Select Case Statement
B.6. If Statements
B.7. VBA Summary
About the Author
Colophon
Index
index_A
index_B
index_C
index_D
index_E
index_F
index_G
index_I
index_L
index_M
index_N
index_O
index_P
index_Q
index_R
index_S
index_T
index_U
index_V
index_W
index_X
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
In a corporate setting, the Microsoft Office Suite
is an

invaluable set of applications. One of Offices'

biggest

advantages is that its applications can work together to
share

information, produce reports, and so on. The problem is,
there
isn't much documentation on their cross-usage. Until
now.
Introducing Integrating Excel and
Access, the unique
reference that shows you how to combine the strengths
of
Microsoft Excel with those of Microsoft Access. In
particular,
the book explains how the powerful analysis tools of Excel
can
work in concert with the structured storage and more
powerful
querying of Access. The results that these two
applications can
produce together are virtually impossible to achieve with
one
program separately.
But the book isn't just limited to Excel and Access.

There's
also a chapter on SQL Server, as well as one dedicated
to
integrating with other Microsoft Office applications. In
no time,
you'll discover how to:
Utilize the built in features of Access
and Excel to access
data
Use VBA within Access or Excel to access
data
Build connection strings using ADO and
DAO
Automate Excel reports including
formatting, functions, and
page setup
Write complex functions and queries with
VBA
Write simple and advanced queries with
the Access GUI
Produce pivot tables and charts with
your data
With Integrating Excel and
Access, you can crunch and
visualize data like never before. It's the ideal guide for
anyone
who uses Microsoft Office to handle data.
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