® ®
Microsoft Excel &
Access™ Integration
with Office 2007
Michael Alexander and
Geoffrey Clark
® ®
Microsoft Excel &
Access™ Integration
with Office 2007
Michael Alexander and
Geoffrey Clark
Microsoft® Excel® & Access™ Integration with Office 2007
Published by
Wiley Publishing, Inc.
10475 Crosspoint Boulevard
Indianapolis, IN 46256
www.wiley.com
Copyright © 2007 by Wiley Publishing, Inc., Indianapolis, Indiana
Published simultaneously in Canada
ISBN: 978-0-470-10488-0
Manufactured in the United States of America
10 9 8 7 6 5 4 3 2 1
No part of this publication may be reproduced, stored in a retrieval system or transmitted
in any form or by any means, electronic, mechanical, photocopying, recording, scanning or
otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copy-
right Act, without either the prior written permission of the Publisher, or authorization
through payment of the appropriate per-copy fee to the Copyright Clearance Center, 222
Rosewood Drive, Danvers, MA 01923, (978) 750-8400, fax (978) 646-8600. Requests to the
Publisher for permission should be addressed to the Legal Department, Wiley Publishing,
Inc., 10475 Crosspoint Blvd., Indianapolis, IN 46256, (317) 572-3447, fax (317) 572-4355, or
online at http://www.wiley.com/go/permissions.
Limit of Liability/Disclaimer of Warranty: The publisher and the author make no repre-
sentations or warranties with respect to the accuracy or completeness of the contents of this
work and specifically disclaim all warranties, including without limitation warranties of fit-
ness for a particular purpose. No warranty may be created or extended by sales or promo-
tional materials. The advice and strategies contained herein may not be suitable for every
situation. This work is sold with the understanding that the publisher is not engaged in ren-
dering legal, accounting, or other professional services. If professional assistance is
required, the services of a competent professional person should be sought. Neither the
publisher nor the author shall be liable for damages arising herefrom. The fact that an orga-
nization or Website is referred to in this work as a citation and/or a potential source of fur-
ther information does not mean that the author or the publisher endorses the information
the organization or Website may provide or recommendations it may make. Further, read-
ers should be aware that Internet Websites listed in this work may have changed or disap-
peared between when this work was written and when it is read.
For general information on our other products and services or to obtain technical support,
please contact our Customer Care Department within the U.S. at (800) 762-2974, outside the
U.S. at (317) 572-3993 or fax (317) 572-4002.
Library of Congress Control Number: 2007007062
Trademarks: Wiley, the Wiley logo, and related trade dress are trademarks or registered
trademarks of John Wiley & Sons, Inc. and/or its affiliates, in the United States and other
countries, and may not be used without written permission. Microsoft, Excel, and Access
are trademarks or registered trademarks of Microsoft Corporation in the United States
and/or other countries. All other trademarks are the property of their respective owners.
Wiley Publishing, Inc., is not associated with any product or vendor mentioned in this book.
Wiley also publishes its books in a variety of electronic formats. Some content that appears
in print may not be available in electronic books.
Mike Alexander
For Mary, Ethan and Emma
Geoffrey Clark
For FairBear and LittlestBear,
the fairest and littlest of all bears in the land...
About the Authors
Mike Alexander is a Microsoft Certified Applications Developer (MCAD)
with over 14 years experience developing Office solutions for a wide array of
Companies and industries. He currently lives in Frisco, Texas, where he works
as a Senior Program Manager for a top technology firm. He is the author of
several books on data analysis using Excel and Access, and he is the principal
behind DataPigTechnologies.com, a site that offers free tutorials on Excel
and Access.
Geoff Clark holds an MBA from Duke University’s Fuqua School of Business
where he graduated as a Fuqua Scholar in 2000. After business school, Geoff
joined McKinsey & Company in Charlotte, NC, as an associate management
consultant. It was at McKinsey & Company where Geoff began applying
advanced Microsoft Excel and Access techniques to aid clients in analytical
problem solving and fact-based decision making. After his stint in manage-
ment consulting, Geoff served as director of marketing analytics with a $700
million environmental services firm. Currently, he is a manager at a major
technology outsourcing company and develops office applications to enable
enterprise-wide metrics and change initiatives. Geoff lives in Plano, TX, with
his wife and son.
v
Credits
Acquisitions Editor Project Coordinator
Katie Mohr Kristie Rees
Development Editor Graphics and Production Specialists
Kelly Talbot Denny Hager
Stephanie D. Jumper
Technical Editor
Jennifer Mayberry
Ed Ferrero
Barbara Moore
Production Editor Ronald Terry
Angela Smith
Quality Control Technicians
Copy Editor John Greenough
Travis Henderson Brian H. Walls
Editorial Manager Proofreading and Indexing
Mary Beth Wakefield Aptara
Production Manager Anniversary Logo Design
Tim Tate Richard Pacifico
Vice President and Executive
Group Publisher
Richard Swadley
Vice President and Executive
Publisher
Joseph B. Wikert
vii
Contents
About the Authors v
Acknowledgments xvi
Introduction xvii
Part I: Basic Integration Techniques 1
Chapter 1: Getting Excel Data into Access 3
Differences Between Access and Excel 4
Different Types of Excel Spreadsheets 4
The Excel Worksheet in Report Format 4
The Excel Worksheet in Flat File Format 5
The Excel Worksheet in Indexed List Format 6
The Access Table 8
The Table in the Datasheet View 8
The Table in the Design View 8
Different Types of Data 9
Table and Field Naming Conventions 11
Bringing Your Excel Data into Access 11
Importing a Worksheet into a New Table 11
Linking an Excel Worksheet to Access 18
Appending an Excel Worksheet to an Existing Table 22
Potential Errors When Using the Append Import Wizard 23
Summary 25
Chapter 2: Analyzing Excel Data with Access Queries 27
Introduction to Access Queries 27
Creating Your First Select Query 28
Sorting Query Results 31
Filtering Query Results 32
ix
x Contents
Querying Multiple Tables 33
Understanding the Concept of Relational Databases 34
Creating a Query that Joins Two Tables 35
Using Operators to Further Refine Your Queries 37
Exporting Query Results 40
Using Aggregate Queries 42
Aggregate Query Basics 42
About Aggregate Functions 44
Group By 45
Sum, Avg, Count, StDev, Var 46
Min, Max, First, Last 47
Expression, Where 47
Using Calculations in Your Analysis 50
Common Calculation Scenarios 50
Using Constants in Calculations 51
Using Fields in Calculations 51
Using the Results of Aggregation in Calculations 52
Using the Results of One Calculation as an Expression
in Another 53
Performing Simple Date Calculations 53
Leveraging Access Query Wizards to Solve Common
Excel Problems 55
The Find Duplicates Query Wizard 55
The Find UnMatched Query Wizard 58
Crosstab Queries 62
Summary 67
Chapter 3: Sprucing Up Excel Data with Access Reports 69
A Closer Look at the Access Report 69
Creating Your First Report 69
Viewing Your Report 71
Report View 72
Layout View 72
Design View 74
Page Footer 76
Report Footer 77
Creating and Modifying Grouped Reports 78
Grouping 78
Sorting and Totaling 80
Customizing Reports with Formatting 83
Page Layout Techniques 83
Solving Page Break Issues 83
Multi-Column Report Layout Example 84
Formatting Techniques 90
Formatting to Make Reports More Readable 91
Formatting to Highlight Specific Results 93
Contents xi
Creating an Individualized Customer Letter 95
Developing the Report Data Feed 95
Initial Design of the Report 96
Adding Finishing Touches for a Professional Look 101
Summary 102
Chapter 4: Using PivotTables and PivotCharts in Access 103
Working with PivotTables in Access 103
PivotTables in Access? 104
The Anatomy of a PivotTable 105
The Totals and Detail Area 105
The Row Area 106
The Column Area 106
The Filter Area 107
Creating a Basic PivotTable 107
Creating an Advanced PivotTable with Details 111
Saving Your PivotTable 113
Sending Your Access PivotTable to Excel 114
PivotTable Options 115
Expanding and Collapsing Fields 116
Changing Field Captions 116
Sorting Data 117
Grouping Data 117
Using Date Groupings 120
Filtering for Top and Bottom Records 121
Adding a Calculated Total 122
Working with PivotCharts in Access 125
The Data Area 125
The Series Area 125
The Category Area 127
The Filter Area 127
Creating a Basic PivotChart 128
Formatting Your PivotChart 130
Summary 132
Chapter 5: Getting Access Data into Excel 135
Different Options for Importing Access Data 135
The Drag-and-Drop Method 136
Exporting the Data from Access 137
Using the Get External Data Menu 140
Using Microsoft Query 143
Introduction to Microsoft Query 143
Using the Microsoft Query Wizard 143
Starting the Query Wizard 144
Choosing and Modifying Your Data with the Query Wizard 145
Going Beyond the Wizard in Microsoft Query 156
Introduction to the Microsoft Query Interface 157
Using Microsoft Query to Import Data 159
xii Contents
Advanced Use of Microsoft Query 164
Using Joins 164
Modifying SQL to Create Custom Fields 167
A Very Brief Primer on SQL 167
Creating the Field 167
Adding User Defined Parameters in Microsoft Query 170
A Simple User-Defined Parameter 171
Advanced Example of User-Defined Parameter 172
Limitations of Microsoft Query 178
The Microsoft Query Wizard 178
Microsoft Query 178
Summary 179
Chapter 6: Leveraging Macros in Excel and Access 181
What Is a Macro? 181
A General Definition 181
Why Use a Macro? 182
Creating User Friendly Interfaces 182
Automating Repetitive Tasks 183
Formatting Cell Ranges 183
Comparing Macros in Excel and Access 183
Creating Macros in Microsoft Excel 184
Creating Macros in Microsoft Access 184
Introducing Excel Macros 184
Using the Macro Recorder 185
The Macro Recorder User Interface 185
Recording Macros with Absolute References 188
Recording Macros with Relative References 190
Macro Security in Excel 2007 193
Default Excel Security Settings 193
The Office Trust Center 194
Macro-Disabled Excel File Extensions 196
Excel Macro Examples 197
Macro for Navigating a Spreadsheet 197
Macro for Formatting 203
Macros in Microsoft Access 206
Macro Security in Access 2007 207
Creating your First Access Macro 209
The Macro Design Template 210
Common Actions in Access Macros 211
Access Macro Example 217
Summary 223
Contents xiii
Part II: Advanced Integration Techniques 225
Chapter 7: VBA Fundamentals 227
What Is VBA? 228
VBA as an Object-Oriented Programming Language 229
Objects and Collections 229
Properties, Methods, and Arguments 230
Extended Analogy of the Object Model 231
The Visual Basic Editor or VBE 232
Project Explorer Window 234
Code Window 234
Code and Events 234
Variables 236
Object Variables 236
Array Variables 237
Constants 237
Declaring Variables 237
Variable Scope 238
Procedures and Functions 238
Procedures 239
Functions 242
VBA Coding Fundamentals 243
Code that Manipulates Objects 244
With...End With Construct 244
For Each-Next Construct 245
Code that Controls Execution 245
For...Next Construct 245
Do Until...Loop Construct 246
Looping Code: Do...While Loop Construct 247
Logical Code: If...Then and If...Then...Else...End If
Constructs 248
Logical Code: Select Case Construct 249
Getting Help with VBA 250
Summary 252
Chapter 8: Using VBA to Move Data Between Excel and Access 255
Understanding ADO Fundamentals 256
The Connection String 256
Declaring a Recordset 258
Return Read Only Data from a Table or Query 259
Return Updateable Data from a Table or Query 260
Writing Your First ADO Procedure 260
Referencing the ADO Object Library 260
Writing the Code 262
Using the Code 264
xiv Contents
Understanding SQL Fundamentals 265
Basic SQL Syntax to Select Data 265
The SELECT Statement 265
Selecting All Columns 266
The WHERE Clause 266
Expanding Your Search with the Like Operator 267
Grouping and Aggregating with the GROUP BY Clause 268
The HAVING Clause 268
Creating Aliases with the AS Clause 269
Setting Sort Order with the ORDER BY Clause 269
SELECT TOP and SELECT TOP PERCENT 270
Writing Your First ADO/SQL Data Extract 270
Using Criteria in Your SQL Statements 272
Set Numeric Criteria 272
Set Textual Criteria 272
Set Date Criteria 273
Set Multiple Criteria 273
Using the LIKE Operator 273
Common Scenarios Where VBA Can Help 275
Query Data from an Excel Workbook 275
Append Records to an Existing Excel Table 278
Append Excel Records to an Existing Access Table 280
Querying Text Files 282
Summary 283
Chapter 9: Exploring Excel and Access Automation 285
Understanding the Concept of Binding 285
Early Binding 286
Late Binding 286
Automating Excel from Access 287
Creating Your First Excel Automation Procedure 287
Automating Data Export to Excel 290
Sending One Recordset to Excel 290
Sending Two Datasets to Two Different Tabs
in the Same Workbook 292
Automating Excel Reports: Without Programming Excel 293
Using Find and Replace to Adjust Macro-Generated Code 300
Running an Excel Macro from Access 301
Optimizing Macro-Generated Code 303
Removing Navigation Actions 303
Deleting Code That Specifies Default Settings 304
Cleaning Up Double Takes and Mistakes 305
Temporarily Disabling Screen Updating 306
Automating Access from Excel 306
Setting the Required References 307
Running an Access Query from Excel 307
Contents xv
Running Access Parameter Queries from Excel 309
Running an Access Macro from Excel 314
Opening an Access Report from Excel 315
Opening an Access Form from Excel 315
Compacting an Access Database from Excel 316
Summary 319
Chapter 10: Integrating Excel and Access with XML 321
Why XML? 321
Understanding XML 322
The XML Declaration 322
Processing Instructions 323
Comments 323
Elements 323
The Root Element 324
Attributes 325
Namespaces 326
Creating a Simple Reporting Solution with XML 327
Exporting XML Data from Access 327
Utilizing XML Data in Excel 330
Creating a Data Entry Process Using XML 332
Creating the Data Entry Schema in Access 332
Setting Up the Data Entry Form in Excel 334
Exporting Results from Excel to XML 335
Getting the Results Back into Access 336
Summary 338
Chapter 11: Integrating Excel and Other Office Applications 339
Integrating Excel with Microsoft Word 339
Creating a Dynamic Link to an Excel Table 340
Getting Excel Data to a Word Document Using Automation 344
Creating a Word Mail Merge Document 346
Simulating the Word Mail Merge Function from Excel 351
Integrating Excel with PowerPoint 354
Creating a PowerPoint Slide with a Title 355
Copying a Range of Cells to a Presentation 357
Sending All Excel Charts to the Presentation 359
Converting a Workbook into a PowerPoint Presentation 361
Integrating Excel and Outlook 364
Mailing the Active Workbook 364
Mailing a Specific Range 366
Mailing to All E-mail Addresses in Your Contact List 367
Saving All Attachments in a Folder 369
Saving Certain Attachments to a Folder 371
Summary 373
Index 375
Acknowledgments
We would like to express our deepest thanks to Katie Mohr and Kelly Talbot,
for all the hours of work put into making this book as clear as it can be. Thanks
also to Ed Ferraro for saving us from embarrassment as he worked to correct
our mistakes and suggest numerous improvements in our examples and text.
Any errors you may find here are ours, not his. We would also like to thank the
brilliant team of professionals who helped bring this book to fruition. Finally,
a special thank you goes out to our families for putting up with all the time we
spent away on this project.
xvi
Introduction
Since Office 2000, Microsoft has marketed the Office suite as a set of interoper-
able applications that easily enable integration of multiple processes. For most
users, however, Office is still a suite of individual applications. Excel is used
for spreadsheet analysis and reporting whereas Access is used for database
functions—and that’s it. Indeed, most mainstream training courses and docu-
mentation are often dedicated to one application or another, often providing
very little information on the concept of integration.
In fact, it seems that most Office users tend to fall in one of two camps: Excel
users and Access users. Very few people operate comfortably in both camps.
Even fewer realize the full integration potential of these powerful Office appli-
cations.
Microsoft Excel reigns supreme among spreadsheet applications. In the cor-
porate world, Excel is a ubiquitous and nearly universal application. This pop-
ularity has come with a severe downside as most business analysts have
constrained themselves to just one tool for all their analytical and reporting
needs — Excel. What’s the problem with that? Well, Excel is not designed to
do many of the activities we try to make it do — like integrating disparate data
sources and performing complex, multi-stage data processing. Rather than
explore the functionality of other Office applications (namely Access), analysts
instead engage in hand-to-hand combat with their data, creating complex
workarounds and inefficient processes.
As a relational database application, Microsoft Access enables integration of
disparate data sources and multi-step data manipulation that would be cum-
bersome or even impossible to do in Excel. However, Access has its limitations
in the business community. First, it does not have a fraction of the popularity
that Excel has. Chances are that nearly everyone in your company, from the
xvii