Excel Hacks, 2nd Edition

Chapter 8. Cross-Application Hacks

Hacks 135–138

With the ever-increasing usage of computers in our society, it is fast becoming necessary for most people to use a combination of applications in their work. Microsoft Office 2007’s enhanced capabilities for cooperation between its applications makes combining Excel with Word, Access, and Outlook easier than before. The hacks in this chapter cover some of the most common problems.

Import Data from Access 2007 into Excel 2007

VBA can be used to import data into Excel from an Access database, but Access 2007 has undergone some major, important changes that effect how we create connections to Access 2007 databases and also how we work with the Access 2007 Objects Model.

To communicate with Access 2007databases via VBA, the preferable method is to use Data Access Objects (DAO), which is the object model written specifically for Access. It’s an object library with a collection of database objects.

Tip

This hack works with Excel 2007, Access 2007, Windows XP, and Windows Vista.

For the purpose of this hack, we will use the database file Northwind 2007.accdb (a database created and stored in Access 2007 that can be used for test purposes).

Tip

If you installed Office 2007 via a downloadable link, you may need to download the Northwind 2007 example file from the Microsoft web site: http://office.microsoft.com/en-us/templates/TC012289971033.aspx ...