ACC: Named Excel Range Must Contain More Than One Cell (186309)


The information in this article applies to:

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

SYMPTOMS

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

CAUSE

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

RESOLUTION

Create a named range that contains the cell with data and at least one extra cell for the range to be recognized by Microsoft Access. This extra cell can be blank. If you create a named range that contains cells in two adjacent columns, the Microsoft Access Import Wizard will allow 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 of this article.
  2. On the Insert menu click, 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 Microsoft Excel, create a new workbook.
  2. Type the following data in Sheet1: 
           A1: TEST1
  3. Select cell Al.
  4. On the Insert menu, click 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, click 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 linking, search the Access Help Index for "Excel, importing and linking Microsoft Excel data," or ask the Office Assistant.

For more information about named ranges, search the Excel Help Index for "named range," select "Name cells in a workbook" from the Topics Found dialog box, or ask the Office Assistant.
Modification Type:MajorLast Reviewed:9/25/2003
Keywords:kbinterop kbprb KB186309
©2003 Microsoft Corporation. All rights reserved.