Share this Page URL

Chapter 4. Importing and Linking to Data > Importing Data - Pg. 111

Importing and Linking to Data 111 When you import data, you can add the data to a table that's already defined or create a new table. We'll step through examples of each of these options. You can import an entire Excel worksheet or a range of cells that you've named in the worksheet. If you need to import data from more than one worksheet in a workbook, you need to import the data from each worksheet separately. Each work- sheet that you import will become its own table (provided you choose that option in the import wizard). Creating a named range in Excel If you're familiar with Excel, you know that you refer to cells by their column and row coordinates. For example, cell C2 is the cell in the third column (C) and the second row. In formulas, you can refer to a range of cells-- for example, = Sum(C2:E2). You can also create a name for a range of cells and then use that name to refer to that group of cells. To create a range name, use the Insert, Name, Define command. If you're going to import data from Excel into Access, you'll benefit from knowing as much as possible about Excel. For more information about Microsoft Excel 2003, see Microsoft Office Excel 2003 Inside Out (Microsoft Press, 2003). Importing Excel Data into an Existing Table The first rule about importing data from Excel into a table already defined in your database is that the column headings in the Excel worksheet must match the field names of the table in Access. Access won't import the file if the column headings and field names don't match. If you are comparing field names in the table with column headings in the spreadsheet, be sure to open the table in Design view rather than in Datasheet view. In Datasheet view, you could be viewing values entered in a field's Caption property rather than actual field names. You also need to consider a field's data type when importing data from a worksheet. In an Excel worksheet, you don't have the control you have in Access to define the type of data that can be