Importing Excel data into Access can seem more complicated, but it really isn't. You just have more options. You can import data directly into an Access table, or you can link to an Excel spreadsheet. What's the difference? Imported data ends up in Access, whereas linked data stays in Excelbut you can still use it in Access. You'll see how this works in the next two sections. Importing Excel Data into an Access Table Importing is usually a simple process of transferring (copying) data from one file to another. Access doesn't change the data in any way. In this section, you'll see how easily you can copy Excel data into an Access table. After the data is in an Access table, changes made to the original data in Excel don't change the imported data in Access. Let's import CatalogsInExcel.xls by doing the following: -
In Access, select Get External Data from the File menu, and then select Import from the resulting submenu. -
In the Files of Type control, select Microsoft Excel Select CatalogsInExcel.xls from the list of files, and then click Import . Or, you could just double-click CatalogsInExcel.xls . You just launched the Import Spreadsheet Wizard. -
The first thing the wizard wants to know is whether you're importing the entire spreadsheet or a named range. In Excel, a named range is a specific set of cells that you can refer to using a name you supply. This spreadsheet doesn't have any named ranges, so keep the Show Worksheets option. If you're curious , check the Show Named Ranges option, but you'll see that the list to the right doesn't change (because no named ranges exist). The bottom panel shows the data. Click Next to continue. -
The next pane lets you determine whether to identify the Excel column headings as field names . We recommend that you check the First Row Contains Column Headings unless you have a specific reason not to. If you don't, Excel will assume the field names are normal entries. After selecting this option, click Next. -
The wizard is flexible and lets you either append the incoming data into an existing table or create a new table. The default is the In a New Table option. Without making any changes in this pane, click Next. -
This next pane lets you do a lot. First, you can rename any field by selecting the field in the bottom display and then entering a new name in the Field Name control. In addition, you can apply an index (you learned about indexes in Chapter 11, "Customizing Your Tables"). If Access isn't sure how to interpret the data, the wizard enables the Data Type control so you can specify the data type for the field. Checking the Do Not Import Field (Skip) option deletes the selected field from the import process. Don't make any changes to the wizard's choicesjust click Next. -
At this point, the wizard wants to help you define a primary key. By default, the wizard suggests that you add an AutoNumber data type field. Instead, select the Choose My Own Primary Key option, and select Name from the drop-down control to the right (it should be selected by default). Then, click Next . (Refer to Chapter 4, "Planning a Database" for information on primary keys.) -
In the final pane, give the new table a name. You already have a table named Catalogs, so change the wizard's default name to CatalogsFromExcel; then click Finish . The wizard displays a message when the wizard has completed the import task. Click OK to clear that message. -
Find the new table in the Database window and open it. Figure 16.3 shows the newly imported data. For the most part, this table is identical to the original Catalogs tableright down to the primary key field. All that traveling really hasn't changed anything. Figure 16.3. The wizard has created a new table and copied the Excel data into it. Linking to Excel Data You don't have to import Excel data to use it in Access. Depending on your needs, you might choose to link to the spreadsheet. Linked data is dynamic , which means it reflects changes. If you change the data in Excel, the linked data reflects that change in Access, and vice versa. To illustrate this flexible and powerful feature, link to CatalogsInExcel.xls by performing the following steps: -
In Access, select Get External Data from the File menu, and then select Link Tables from the resulting submenu. -
In the Files of Type control, select Microsoft Excel , select CatalogsInExcel.xls in the list of files, and then click Link . Or, you can just double-click CatalogsInExcel.xls . (You can also use the Look In control to locate files in another folder, but we don't need to do this for our current example.) You just launched the Link Spreadsheet Wizard. -
The first thing the wizard wants to know is whether you're linking to the entire spreadsheet or a named range. Keep the Show Worksheets option, and click Next to continue. -
The next pane lets you determine whether to identify the Excel column headings as field names. We recommend that you check the First Row Contains Column Headings unless you have a specific reason not to. If you don't, Excel assumes the field names are normal entries. After selecting this option, click Next. -
In the final pane, name the new linked table CatalogsFromExcelLinked , click Finish , and then click OK to clear the resulting confirmation message. The Database window displays the link along with the tables, as shown in Figure 16.4, but the icon to the left of the linked data clearly identifies it as a linked Excel table. Figure 16.4. The Database window identifies the linked data source as an Excel file. Feel free to open the linked spreadsheet in Access and change data. Then, open the spreadsheet in Excel and you'll see that the spreadsheet reflects the changes you made. Make a change in the spreadsheet, save it, and then close it. Open the linked spreadsheet in Access to view changes you made to the spreadsheet in Excel. |