Import or link data from a spreadsheet

Note   You can link a table only in a Microsoft Access database, not a Microsoft Access project.

Before you proceed, make sure that the data in the spreadsheet is arranged in an appropriate tabular format, and the spreadsheet has the same type of data in each field (column) and the same fields in every row.

1. Open a database, or switch to the Database window for the open database.
   
   
2. To import a spreadsheet, on the File menu, point to Get External Data, and then click Import.

   To link a spreadsheet, on the File menu, point to Get External Data, and then click Link Tables.

3. In the Import (or Link) dialog box, in the Files Of Type box, select Microsoft Excel (*.xls) or Lotus 1-2-3 (*.wk*).
   
   
4. Click the arrow to the right of the Look In box, select the drive and folder where the spreadsheet file is located, and then double-click its icon.

   Important   If you link to a file on a local area network, make sure that you use a universal naming convention (UNC) path, instead of relying on the drive letter of a mapped network drive in Windows Explorer. A drive letter can vary on a computer, or it may not always be defined; whereas, a UNC path is a reliable and consistent way for Microsoft Access to locate the data source that contains the linked table.

5. Follow the directions in the Import Spreadsheet Wizard dialog boxes. If you are importing from a Microsoft Excel version 5.0 or later workbook, then you can import from one worksheet within a workbook. You cannot import from any other multiple-spreadsheet files, such as Microsoft Excel version 4.0 workbooks or Lotus notebooks. To import from these files, you must first save each spreadsheet as an individual file.

Notes

• You can import or link all the data from a spreadsheet, or just the data from a named range of cells. Although you normally create a new table in Microsoft Access for the data, you can also append the data to an existing table as long as your spreadsheet column headings match the table's field names.
  
  
• If importing a spreadsheet takes an unexpectedly long time, it might be because many errors are occurring. To cancel importing, press CTRL+BREAK.
  
  
• Microsoft Access attempts to assign the appropriate data type to imported fields, but you should check your fields to make sure that they are set to the data type you want. For example in an Access database, a phone number or postal code field might be imported as a Number field, but should be changed to a Text field in Microsoft Access because it is unlikely that you will perform any calculations on these types of fields. You should also check and set field properties, such as formatting, as necessary.
  
  
• You can import data from other programs' spreadsheets, as long as they are in the Microsoft Excel or Lotus 1-2-3 format.