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

10 9 8 7 6 5 4

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 Copyright 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 representations 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 fitness for a particular purpose. No warranty may be created or extended by sales or promotional 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 rendering 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 organization or Website is referred to in this work as a citation and/or a potential source of further 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, readers should be aware that Internet Websites listed in this work may have changed or disappeared 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 management 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.

Credits

Acquisitions Editor

Katie Mohr

Development Editor

Kelly Talbot

Technical Editor

Ed Ferrero

Production Editor

Angela Smith

Copy Editor

Travis Henderson

Editorial Manager

Mary Beth Wakefield

Production Manager

Tim Tate

Vice President and Executive Group Publisher

Richard Swadley

Vice President and Executive Publisher

Joseph B. Wikert

Project Coordinator

Kristie Rees

Graphics and Production Specialists

Denny Hager

Stephanie D. Jumper

Jennifer Mayberry

Barbara Moore

Ronald Terry

Quality Control Technicians

John Greenough

Brian H. Walls

Proofreading and Indexing

Aptara

Anniversary Logo Design

Richard Pacifico

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.

Introduction

Since Office 2000, Microsoft has marketed the Office suite as a set of interoperable 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 documentation 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 applications.

Microsoft Excel reigns supreme among spreadsheet applications. In the corporate world, Excel is a ubiquitous and nearly universal application. This popularity 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 cumbersome 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 president to the recent graduate new hire, has some working knowledge of Excel. How many company presidents have an Access database on their laptops, or would know what to do with it if they did? Furthermore, training for Access has rarely targeted the community of business analysts, choosing instead to cater to database and application builders. How will a working knowledge of user-subforms or knowing how to set up a contact database help a struggling analyst synthesize financial data from three different legacy IT systems? Understandably, the business analyst community of Excel power users have stuck with the tools they know and steered clear of those they do not.

In so doing, however, they have left a lot of potential productivity off the table. It is our aim with this book to help the Excel user (and the Access user) harness that productivity by using and integrating both Excel and Access. Through integration, Access gains the benefit of flexible presentation layer and versatile analysis capabilities in Excel; Excel gains the benefit of relational database structure and robust querying tools in Access. Integrating both applications can yield incredible productivity gains and enable penetrating business analyses.

What this book will highlight is that there are powerful functionalities in both Excel and Access that can help you go beyond the one dimensional world of either spreadsheet or database. This book will liberate you from the daily grind of routine data practices and help you to explore all the functionality that exists when Excel and Access are brought together.

Although both of these applications are powerful tools on their own, combining the functionality of Excel and Access opens up a whole new world of possibilities

What to Expect from This Book

In this book, you will find a comprehensive review of the wide range of integration techniques that can be performed using Excel and Access. Through step-by-step instruction, you will explore the benefits of integrating Excel and Access, the differences and similarities between Excel and Access, and some of the common pitfalls of moving and analyzing data across applications. After reading this book, you will be able to:

Easily move data between Excel and Access

Store Excel data in a structured, relational database

Use Excel pivot tables with Access data

Report Access data using the Excel presentation layer

Move data between Excel and Access using VBA, ADO and SQL

Automate redundant processes using VBA to save time and increase productivity

Simplify integration tasks using XML

Integrate Excel data into other Office applications

Skills Required for This Book

In order to get the most out of this book, you have the following skills:

Some experience working with data and familiarity with the basic concepts of data analysis such as working with tables, aggregating data, and performing calculations.

Experience using Excel with a strong grasp of concepts such as table structures, filtering, sorting and using formulas.

Some basic knowledge of Access, enough to know it exists and to have opened a database once or twice

How This Book Is Organized

This book is organized into two parts: Basic Integration Techniques and Advanced Integration Techniques.

Part I: Basic Integration Techniques

Part I of this book is dedicated to those integration techniques that can be accomplished via the Excel and Access user interfaces. This part includes Chapters 1 through 6. Chapters 1 through 4 will teach you how to get your Excel data into Access as well as how to leverage the many Access utilities to go beyond the functionality found in Excel. Chapter 5 demonstrates the best ways to use Access data in Excel. Chapter 6 introduces you to the world of macros, demonstrating automation techniques from both an Excel and Access point of view.

Part II: Advanced Integration Techniques

Part II of this book is dedicated to automating your integration processes via VBA (Visual Basic for Applications) code. Chapter 7 provides a high level introduction to VBA. Chapter 8 covers the various methods of moving data between Excel and Access using VBA. In Chapter 9, you will learn how to control and manipulate Excel from Access and vice versa. Chapter 10 introduces you to the world of XML; discussing several ways XML can help you simplify your integration tasks. Finally, Chapter 11 rounds out this book with a look into the various ways you can integrate Excel with Word, PowerPoint and Outlook.

Companion Database

The examples demonstrated throughout this book can be found in the companion database. This sample database is located at www.wiley.com.

PART I

Basic Integration Techniques

CHAPTER 1

Getting Excel Data into Access

The business world is split between Access users and Excel users. Access users tend to be the database guys and the techie application builders. Everybody else uses Excel. If you work in corporate America, you are probably conversant in the language of Excel. You can send even the most seasoned of senior vice presidents a spreadsheet-style report and trust she will know what to do with it. Send that same VP an Access file and ask her to run a crosstab query, and you’ve relegated your career to the cubicle farm.

Simply put, Excel has few equals when it comes to performing large numbers of calculations based on other cells and then presenting that information in a widely acceptable and familiar format. However, as a relational database application, Access is a powerful tool that gives the user the ability to integrate disparate data sources to perform analysis and reporting that would be cumbersome or even impossible to do in Excel. Integrating both applications brings the best of each to bear on a plethora of business analyses. Yet few professionals in corporate America and elsewhere have the experience necessary to accomplish this.

Our goal is to introduce the power of Microsoft Access to Excel users of all skill levels. We want to show you how integrating Access and Excel can improve and expand your abilities in data analysis and reporting beyond what is possible by simply using Excel. In so doing, you will become an indispensable team member with a very rare set of skills.

This chapter explores the first steps in integrating these two Microsoft Office applications for data analysis. The first section describes the relevant differences between Access and Excel for the Access uninitiated. The remaining sections include a hands-on exercise on getting Excel data into Access and is followed by tips and tricks about how to optimize your new database’s performance.

Differences Between Access and Excel

Before you dive in to bringing Excel data into Access, take a closer look at each application. Specifically, you need a better understanding of just what the utility is of each application and how they are different. You’ll start by examining different categories of spreadsheets in Excel.

Different Types of Excel Spreadsheets

Not all spreadsheets are created equal. Intuitively, you probably already know this. Before importing Excel data into Access, however, you should familiarize yourself with the different types of worksheets.

At the risk of oversimplification, Excel worksheets (not applications) come in three fundamental forms: the Excel report, the Excel flat data file, and the Excel indexed list. Only the last two types are compatible with Access. To ensure your Excel data is in the proper format to be used in Access, you must understand these three types of spreadsheets.

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!