Sebastopol, CA--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 (VBA)."
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 it."
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 desktops."
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."
Additional Resources:
- Chapter 3, "Data Access from Excel VBA"
- More information about the book, including table of contents, index, author bio, and samples
- A cover graphic in JPEG format
Integrating Excel and Access
Michael Schmalz
ISBN: 0-596-00973-9, 216 pages, $39.95 US
order@oreilly.com
1-800-998-9938; 1-707-827-7000
About O’Reilly
O’Reilly Media spreads the knowledge of innovators through its books, online services, magazines, and conferences. Since 1978, O’Reilly Media has been a chronicler and catalyst of cutting-edge development, homing in on the technology trends that really matter and spurring their adoption by amplifying “faint signals” from the alpha geeks who are creating the future. An active participant in the technology community, the company has a long history of advocacy, meme-making, and evangelism.