Book Review: Integrating Excel and Access

O’Reilly’s press release reads – Despite Microsoft’s insistence that Office has evolved from a suite of personal productivity products to a “comprehensive and
integrated system” in which applications such as Excel and Access are interoperable, the majority of tutorials and other resources continue to focus on the use of each application individually. Consultant Michael Schmalz, author of “Integrating Excel and Access” (O’Reilly, US $39.95), discovered that he was not alone in seeking a book on how to create custom applications that combine the functions of separate Office products.
“While Excel is a powerful tool on its own, people can do a lot more when
they add the power of a relational database,” Schmalz asserts. “Combining
Excel and Access opens up a whole new range of possibilities. In my work
as a consultant, I found that those skills are in demand in many
departments–from Human Resources to Operations to Finance.”
With this new book, Schmalz took it upon himself to provide a resource
that specifically teaches business people how to use spreadsheets, graphs
and other powerful analysis tools of Excel with the structured storage and
querying capabilities of Access. The goal is to create data analysis
applications that will improve their reporting. “They’ll be able to use
the two programs together to share data, with or without programming,” he
explains. “Along the way, the book will also introduce programming topics
that will help them sharpen their skills in Visual Basic for Applications
Schmalz outlines a typical scenario in which the senior management of a
company that stores sales information in Access wants to know how each
salesperson, sales manager, and region performs on a daily basis. “Since
they want to see the reports so frequently, it’s necessary to automate
these reports as much as possible,” he points out. “This book shows
analysts how to gather the information and build the reports, charts, and
supporting details necessary to meet these business objectives.”
Those skills apply to several uses of corporate data, such as producing
financial reports, invoices, monthly commission schedules, trend
information to aid corporate planning, populating financial models and
storing results, and performing data analysis on average profit per sale,
sales by month, and much more. “A lot of companies are trying to make
better use of their data,” Schmalz contends. “They already have the tools
they need. With this book, I show them how to access their data through
Excel or Access and get it into a format that will allow them to analyze
“Integrating Excel and Access” teaches readers several ways to use Excel,
Access and VBA to create applications that look good, work well, and
produce they results they need. They’ll learn to read Access data from
Excel and vice versa, use VBA to control Excel from Access, build queries
from data in Excel spreadsheets, generate graphs from Access using Excel’s
tools, create Access reports based on Excel spreadsheets, use Excel Forms
to create interfaces when Access isn’t available, and generate PivotTables
and PivotCharts with VBA.
“From my experience, Access and Excel allow for the most benefit from
integration among Office applications,” says Schmalz, who worked for MBNA
and other banks before becoming a consultant. “But I’ve also included a
chapter on SQL Server and a chapter on ways to integrate with Word,
PowerPoint, and MapPoint that allows for even more flexibility. There is
also information on packaging Office-based applications to work across
“Integrating Excel and Access” also includes a project that walks readers
through the steps to solve a business problem. Although the screenshots in
the book were produced from Microsoft Office 2003, most of the topics can
be used with Office 97, 2000, and XP as well. Schmalz assumes that readers
have a basic knowledge of Excel and are familiar with Excel macros, but no
experience with Access is necessary.
“After practicing the skills this book illustrates,” Schmalz says,
“they’ll have the necessary knowledge to tackle their most demanding
reporting issues.”