Integrating Excel and Access

Description

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.

Preface

1. Introduction to Access/Excel Integration

     Communications Between Excel and Access

     Automation Objects

     ADO and DAO

     Tackling Projects

     Designing Applications

2. Using the Excel User Interface

     Using External Data

     Using Database Queries

     Returning a PivotTable to Excel

     Using Microsoft Query to Gather Data

     Keeping the Query Updated with VBA

3. Data Access from Excel VBA

     Writing a Reusable Module for Data Access

     Choosing Between ADO and DAO

     CopyFromRecordset Versus Looping

     Formatting Techniques

     Formatting Techniques Example

4. Integration from the Access Interface

     Importing Excel Data

     Linking Excel Data

     Using Export and Analyzing It with Microsoft Office Excel

     Using Raw Exported Access Data in Excel

     Exporting an Access Report to Excel

5. Using Access VBA to Automate Excel

     High-Level Excel Objects

     Other Excel Objects

     Writing and Using Queries in VBA

     Referencing Sheets, Ranges, and Cells

     Writing Excel Worksheet Functions from Access VBA

6. Using Excel Charts and Pivot Tables with Access Data

     Automating Pivot Tables

     Building a Regular Chart

     Using an Array Formula

     Graphing Variables in a Model

7. Leveraging SQL Server Data with Microsoft Office

     Pass-Through Queries Versus Linked Tables

     Creating a Connection Using VBA

     Building the Connection in Access

     Pulling Data in with Excel Alone

     Using DTS to Automate Excel

     Crosstab Queries on SQL Server

8. Advanced Excel Reporting Techniques

     Writing Flexible Formulas

     Changing Data in an Existing Report

     Creating a Report from Scratch

     Using an Access Table for Reporting

9. Using Access and Excel Data in Other Applications

     Automating Microsoft Word

     Getting Information from Microsoft Word

     Automating PowerPoint

     Using Data in MapPoint

10. Creating Form Functionality in Excel

     Working with the UserForm

     Accepting Parameters

     Other Useful Items

11. Building Graphical User Interfaces

     Setting Up a Form

     Using Events

     Using Data

     Adding Buttons

     Tab Order

12. Tackling an Integration Project

     The Project Description

     Main Menu

     Customer Information

     Billing and Payment Information

     Contact History

     Services and Charges

     Expense Entry

     Invoices

     Letters

     Income Statements

A. Excel Object Model

B. VBA Basics

Index