Microsoft KB Archive/205703

= ACC2000: Can't Import Single Cell Excel Named Range =

Article ID: 205703

Article Last Modified on 6/28/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



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:  Follow steps 1 through 7 in the "Steps to Reproduce Behavior" section later in this article. On the Insert menu, point to 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 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, point to 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, point to 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">