Microsoft KB Archive/186309

= ACC: Named Excel Range Must Contain More Than One Cell =

Article ID: 186309

Article Last Modified on 1/23/2007

-

APPLIES TO


 * Microsoft Access 97 Standard Edition

-



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:

 Follow steps 1 through 7 in the "Steps to Reproduce Behavior" section of this article. On the Insert menu click, Name, and then click Define. In the Names In Workbook box, click TEST1.  Under Refers To, change

      =Sheet1!$A$1

to read:

      =Sheet1!$A$1:$B$1  Save and close the workbook.</li> Close Microsoft Excel.</li> Follow Steps 9 through 13 of the "Steps to Reproduce Behavior" section.

Note that the named range "TEST1" now appears in the list box.</li> Click Next three times to bring the dialog box to the "Field options" screen.</li> Select Field2 and click the "Do not import field (Skip)" option.</li> Click Finish.</li></ol>

<div class="moreinformation_section">

Steps to Reproduce Behavior
<ol> In Microsoft Excel, create a new workbook.</li>  Type the following data in Sheet1:

<pre class="fixed_text">      A1: TEST1 </li> Select cell Al.</li> On the Insert menu, click Name, and then click Define.</li> Under Names In Workbook, type TEST1 .</li> Click OK.</li> Save the workbook as TEST.XLS.</li> Close Microsoft Excel.</li> In Microsoft Access, open the sample database, Northwind.mdb.</li> On the File menu, click Get External Data, and then click Import.</li> <li>Under Files Of Type, click Microsoft Excel(*.xls).</li> <li>Locate TEST.XLS and click Import.</li> <li>In the Import Spreadsheet Wizard, click Show Named Ranges.

Note that the named range TEST1 does not appear in the list box.</li></ol>

<div class="references_section">