Chapter 20. Using Access with Microsoft Excel

Spreadsheet and word-processing applications dominate the Windows productivity application software market. According to industry reports, more than 70 percent of all Windows installations in the business environment include a spreadsheet application. Microsoft Office 97, which includes 32-bit Excel 97 and Word 97, is likely to take over the dominant position of 16-bit Microsoft Office 4+ as Windows 95 and Windows NT 4.0 Workstation gain popularity. Spreadsheets include the capability to emulate some features of database managers, such as sorting ranges of cells that are defined as a worksheet "database."

One of the principal uses of Access 97 is the conversion of data in large worksheets to tables in a relational database structure. Some of the primary justifications for converting from the familiar worksheet model to an Access 97 database include the following:

• Access queries provide much greater flexibility in selecting and sorting data than is offered by the limited sort and selection criteria of spreadsheet applications.

• You can create Access forms to simulate common business forms, which is difficult or impossible to do with present-day Windows spreadsheet applications. Excel 97 forms (dialog sheets in Excel 95), for example, offer the choice of only a few simple unbound control objects. Data-entry validation is much easier in Access than in worksheets.

• Access offers many more options for printing formatted reports from your data than are available with worksheets.

• Access VBA allows you to write programs in a full-featured language and does not restrict you to using a set of predetermined worksheet functions. (Spreadsheet applications that use VBA or other BASIC-like macro languages, however, don't suffer from this restriction.)

• Properly designed Access relational databases minimize the duplication of information, speed data entry, and reduce disk file-storage requirements for large aggregations of data.

In many situations, however, changing a worksheet to a database is impractical. You may need to be able to view, import, or edit data that is contained in a worksheet within an Access application. In this case, linking the spreadsheet as an OLE object is the best method. You also can attach worksheet files in Excel 5+ formats to Access 97 tables with the Excel ODBC driver that is supplied with Microsoft Office.

The limited utility of attached worksheet files is demonstrated in the first sections of this chapter by the conversion process necessary to restructure the data to relational form. The second major topic of this chapter, Using Excel as an OLE Server describes how to link or embed worksheet data as OLE objects in bound or unbound object frames.