Integrating Excel and Access

Preface

The Microsoft Office Suite is, in my opinion, the most useful set of applications in a corporate setting. Besides being easy to use and practically perfecting the "What-You-See-Is-What-You-Get" (WYSIWYG) display, the applications in Microsoft Office can work together to share information, produce reports, etc. The problem is that while there are many references available to help users develop their skills in any of the applications, there are few references available to show how to use the applications together. I did quite a bit of integration work as both a consultant and employee at various companies, and I quickly found those skills in demand in many departments—from Human Resources to Operations to Finance. As people asked me to do things that I had never done before and I figured out how to do them, I began building a base of code and knowledge that I could use to solve problems. I kept thinking, "I wish there were a book that could show me how to..." and then, when I had figured out how to do those things, I thought, "I could write that book."

Based on my experience, Microsoft Access and Microsoft Excel allow for the most benefit from integration, so this book focuses mainly on these applications. In addition to Access and Excel, I have included a chapter on SQL Server and a chapter on integrating with other Microsoft Office applications. In each topic, I show how integrating features in different applications can solve problems. Although the examples use rather generic data, you will be able to apply the same concepts to your own data.

The difficulty in writing a book like this lies in tailoring the skill level to fit a wide audience. For example, many of the Excel GUI features might seem very basic to some readers, while they are new to others. In addition, some readers might be very comfortable with Visual Basic for Applications (VBA), and others may have anywhere from no experience with VBA to experience only using the macro recorder. As much as possible, I have tried to build from the basics to the complex when covering each topic. I hope that you will be neither bored nor overwhelmed as you go through the topics.

Who Should Read This Book

Integrating Excel and Access will be useful to people who use Microsoft Office to handle data. This book will show you how Access and Excel can work together to improve your reporting and data analysis. Along the way, it will also introduce many programming topics that will help you sharpen your skills in VBA. While you do not need to be a programming expert, this book assumes that you have basic knowledge of Excel and that you are somewhat familiar with Excel macros. Any experience with Access will be helpful; however, you do not need that experience to learn from this book. Excel power-users will find ways that Microsoft Access can be used to increase the power of their applications. Also, you may find that something that you were doing in Excel is better done in Access, or vice versa.

After reading this book, you will understand how to do the following:

• Utilize the built-in features of Access and Excel to access data

• Use VBA within Access or Excel to access data

• Build connection strings using ADO and DAO

• Access data in a corporate data warehouse, such as SQL Server

• Automate Excel reports, including formatting, functions, and page setup

• Write complex functions with VBA

• Write simple and advanced queries with the Access GUI

• Write queries with VBA

• Produce pivot tables and pivot charts with your data

• Use your data in other Office applications

After these topics have been introduced, the book ends with a project that walks you through the steps to solve a business problem. After practicing the skills this book illustrates, you will have the necessary knowledge to tackle some of your most demanding reporting issues.

The book is organized to build on topics in a logical sequence. However, if you are trying to solve a specific issue, such as writing formulas in VBA, you can skip to the appropriate section. In addition, the code examples in the book illustrate each of the topics where VBA is used. All of the screenshots are produced from MS Office 2003, so your screen may look different, depending on which operating system you use, but most of the topics can be used in Office 97, 2000, and XP as well.

If you are interested in stretching your skills in the individual applications, I suggest reading Access Hacks and Excel Hacks, also from O'Reilly. Both books give great examples of how to use the applications to tackle problems.