Microsoft' Excel &
Access™ Integration
with Office 2 0 0 7
Michael Alexander and Geoffrey Clark
J 1 8 0 7 ; pWIUEYB
Wiley Publishing, Inc.
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
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
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
