ACC2000: Can't Import Single Cell Excel Named Range (205703)


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q205703
Novice: Requires knowledge of the user interface on single-user computers.

This article applies only to a Microsoft Access database (.mdb).

SYMPTOMS

In Microsoft Access, you can import a named range from a Microsoft Excel worksheet. However, you cannot import a named range limited to a single cell.

CAUSE

Although Excel can create a named range consisting of a single cell, Access cannot recognize such a range.

RESOLUTION

Create a named range that contains the cell with data and at least one extra cell so that Access can recognize the range. This extra cell may be blank. If you create a named range that contains cells in two adjacent columns, the Access Import Wizard allows you to choose a field to exclude from the import action.

To reset a range so that it contains more than one cell, follow these steps:
  1. Follow steps 1 through 7 in the "Steps to Reproduce Behavior" section later in this article.
  2. On the Insert menu, point to Name, and then click Define.
  3. In the Names In Workbook box, click Test1.
  4. Under Refers To, change 
       =Sheet1!$A$1
    to read:
       =Sheet1!$A$1:$B$1
  5. Save and close the workbook.
  6. Close Microsoft Excel.
  7. Follow Steps 9 through 13 of the "Steps to Reproduce Behavior" section.

    Note that the named range "Test1" now appears in the list box.
  8. Click Next three times to bring the dialog box to the Field options screen.
  9. Select Field2 and click the Do not import field (Skip) option.
  10. Click Finish.

MORE INFORMATION

Steps to Reproduce Behavior

  1. In Excel, create a new workbook.
  2. Type the following data in Sheet1:
       A1: TEST1
  3. Select cell Al.
  4. On the Insert menu, point to Name, and then click Define.
  5. Under Names In Workbook, type Test1.
  6. Click OK.
  7. Save the workbook as Test.xls.
  8. Close Microsoft Excel.
  9. In Microsoft Access, open the sample database, Northwind.mdb.
  10. On the File menu, point to Get External Data, and then click Import.
  11. Under Files Of Type, click Microsoft Excel (*.xls).
  12. Locate Test.xls and click Import.
  13. In the Import Spreadsheet Wizard, click Show Named Ranges.

    Note that the named range "Test1" does not appear in the list box.

REFERENCES

For more information about importing data, click Microsoft Access Help on the Help menu, type import data from a spreadsheet in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about named ranges, click Microsoft Access Help on the Help menu, type name cells in a workbook in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
Modification Type:MajorLast Reviewed:6/28/2004
Keywords:kbinterop kbprb KB205703
©2004 Microsoft Corporation. All rights reserved.