Integrating Excel and Access / Edition 1

Integrating Excel and Access / Edition 1

5.0 2
by Michael Schmalz
     
 

View All Available Formats & Editions

ISBN-10: 0596009739

ISBN-13: 2900596009730

Pub. Date: 11/10/2005

Publisher: O'Reilly Media, Incorporated

To help database users take advantage of the Excel spreadsheet program in the popular Microsoft Office suite and spreadsheet users become comfortable with its Access database, a Microsoft products consultant explains how to integrate the applications for customized work solutions. A sample integration project offers tips for creating business forms. The guide

Overview

To help database users take advantage of the Excel spreadsheet program in the popular Microsoft Office suite and spreadsheet users become comfortable with its Access database, a Microsoft products consultant explains how to integrate the applications for customized work solutions. A sample integration project offers tips for creating business forms. The guide concludes with information on using the Visual Basic for Applications (VBA) object model to automate Excel, and a short tutorial on some basic VBA programming structures. Annotation © 2006 Book News, Inc., Portland, OR

Product Details

ISBN-13:
2900596009730
Publisher:
O'Reilly Media, Incorporated
Publication date:
11/10/2005
Edition description:
NE
Pages:
236

Related Subjects

Table of Contents

Preface;
Who Should Read This Book;
What's in This Book;
Conventions in This Book;
References in VBA;
We'd Like Your Feedback!;
Safari Enabled;
Acknowledgments;
Chapter 1: Introduction to Access/Excel Integration;
1.1 Communications Between Excel and Access;
1.2 Automation Objects;
1.3 ADO and DAO;
1.4 Tackling Projects;
1.5 Designing Applications;
1.6 Next Steps;
Chapter 2: Using the Excel User Interface;
2.1 Using External Data;
2.2 Using Database Queries;
2.3 Returning a PivotTable to Excel;
2.4 Using Microsoft QueryMicrosoft Query to Gather Data;
2.5 Keeping the Query Updated with VBA;
2.6 Next Steps;
Chapter 3: Data Access from Excel VBA;
3.1 Writing a Reusable Module for Data Access;
3.2 Choosing Between ADO and DAO;
3.3 CopyFromRecordset Versus Looping;
3.4 Formatting Techniques;
3.5 Formatting Techniques Example;
3.6 Summary;
Chapter 4: Integration from the Access Interface;
4.1 Importing Excel Data;
4.2 Linking Excel Data;
4.3 Using Export and Analyzing It with Microsoft Office Excel;
4.4 Using Raw Exported Access Data in Excel;
4.5 Exporting an Access Report to Excel;
4.6 Next Steps;
Chapter 5: Using Access VBA to Automate Excel;
5.1 High-Level Excel Objects;
5.2 Other Excel Objects;
5.3 Writing and Using Queries in VBA;
5.4 Referencing Sheets, Ranges, and Cells;
5.5 Writing Excel Worksheet Functions from Access VBA;
5.6 Chapter Summary and Next Steps;
Chapter 6: Using Excel Charts and Pivot Tables with Access Data;
6.1 Automating Pivot Tables;
6.2 Building a Regular Chart;
6.3 Using an Array Formula;
6.4 Graphing Variables in a Model;
Chapter 7: Leveraging SQL Server Data with Microsoft Office;
7.1 Pass-Through Queries Versus Linked Tables;
7.2 Creating a Connection Using VBA;
7.3 Building the Connection in Access;
7.4 Pulling Data in with Excel Alone;
7.5 Using DTS to Automate Excel;
7.6 Crosstab Queries on SQL Server;
7.7 SQL Server Summary;
Chapter 8: Advanced Excel Reporting Techniques;
8.1 Writing Flexible Formulas;
8.2 Changing Data in an Existing Report;
8.3 Creating a Report from Scratch;
8.4 Using an Access Table for Reporting;
8.5 Putting It Together;
Chapter 9: Using Access and Excel Data in Other Applications;
9.1 Automating Microsoft Word;
9.2 Getting Information from Microsoft Word;
9.3 Automating PowerPoint;
9.4 Using Data in MapPoint;
9.5 Summary;
Chapter 10: Creating Form Functionality in Excel;
10.1 Working with the UserForm;
10.2 Accepting Parameters;
10.3 Other Useful Items;
10.4 Next Steps;
Chapter 11: Building Graphical User Interfaces;
11.1 Setting Up a Form;
11.2 Using Events;
11.3 Using Data;
11.4 Adding Buttons;
11.5 Tab Order;
11.6 Next Steps;
Chapter 12: Tackling an Integration Project;
12.1 The Project Description;
12.2 Main Menu;
12.3 Customer Information;
12.4 Billing and Payment Information;
12.5 Contact History;
12.6 Services and Charges;
12.7 Expense Entry;
12.8 Invoices;
12.9 Letters;
12.10 Income Statements;
12.11 Project Summary;
Appendix A: Excel Object Model;
A.1 Application Object;
A.2 Workbook and Worksheet Objects;
A.3 Excel Object Model Summary;
Appendix B: VBA Basics;
B.1 Dim and Set Statements;
B.2 Loops;
B.3 With Statement;
B.4 Goto Statement;
B.5 Select Case Statement;
B.6 If Statements;
B.7 VBA Summary;
Colophon;

Customer Reviews

Average Review:

Write a Review

and post it to your social network

     

Most Helpful Customer Reviews

See all customer reviews >

Integrating Excel and Access 5 out of 5 based on 0 ratings. 2 reviews.
Anonymous More than 1 year ago
Guest More than 1 year ago
Do you use Microsoft Office to handle data? If you do, then this book is for you! Author Michael Schmalz, has done an outstanding job of writing a book that will show you how Access and Excel can work together to improve your reporting and data analysis. Schmalz, begins by introducing the general topics in the book and explains some of the thought process that goes into integrating the applications. Then, the author covers the tasks that you can complete using only the Excel GUI, as well as discussing some VBA topics. He continues by covering the use of ADO and DAO along with VBA to pull data into Excel. Next, the author covers the use of Excel data in Access and exporting Access data into Excel from features in the Access GUI. Then, he covers controlling Excel from Access and pushing data into Excel. He also covers building charts and pivot tables in Excel using data that originates in Access. Next, the author covers using SQL Serer Data, as well as using DTS and ActiveX scripts to automate Office applications from SQL Server. Then, he covers using VBA from Access to automate reporting in Excel. The author continues by covering data integration and automation from Access and/or Excel in Word, PowerPoint, and MapPoint. He then covers how to build forms in Excel similar to those in Access. The author also covers some basic topics to help you build a functional GUI in Access. Finally, the author covers a project, complete with source code, that requires integration of Access and Excel. In this excellent book is organized to build on topics in a logical sequence. After practicing the skills this book illustrates, you will have the necessary knowledge to tackle some of your most demanding reporting issues.