Microsoft Excel and Access Integration with Office 2007 P1

Chia sẻ: Vong Phat | Ngày: | Loại File: PDF | Số trang:30

Thêm vào BST

Báo xấu

140
lượt xem 17
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

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.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Microsoft Excel and Access Integration with Office 2007 P1

® ® 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