ACC1x: "Invalid Range" Error When Importing from MS Excel

ID: Q88161


The information in this article applies to:
  • Microsoft Access versions 1.0, 1.1
  • Microsoft Excel for Windows, versions 3.0, 3.0a, 4.0, 4.0a


SYMPTOMS

When you import the named range "Database" from Microsoft Excel versions 3.x and 4.x files, you may receive the following error message:

Invalid Range.

This error does not occur when you import from Microsoft Excel version 2.0.


CAUSE

This error is caused by the difference in the Microsoft Excel binary information file format (BIFF) structures between versions:

  • BIFF2 has the range listed in its internally defined named range table. Microsoft Access can read this table.


  • BIFF3 and BIFF4 use an internal construct, rather than listing "Database" in the internal named range table. Microsoft Access cannot read the internal construct of BIFF3 and BIFF4.



RESOLUTION

There are three recommended workarounds that enable you to import data successfully from Microsoft Excel:

  • Save the Microsoft Excel file in 2.x format (in the File Save As dialog box) before importing the file into Microsoft Access.


  • Give the range "Database" a second name (such as "My_Database_Range"). Microsoft Excel allows users to assign multiple names to the same range.


  • Use the range specification rather than a named range (for example, A1:D50).



STATUS

Microsoft has confirmed this to be a problem in Microsoft Access version 1.0. This problem no longer occurs in Microsoft Access version 1.1.


MORE INFORMATION

Steps to Reproduce Behavior


  1. In Microsoft Excel version 3.0 or later, create a named range called "Database" (without the quotation marks).


  2. Save the file as TEST.XLS.


  3. In a Microsoft Access database, choose Import from the File menu.


  4. Select Microsoft Excel, and then choose OK.


  5. Select TEST.XLS, and choose Database for the named range.


Keywords : kberrmsg kbinterop IntpOff IsmExl4
Version : 1.0 1.1
Platform : WINDOWS
Issue type : kbprb


Last Reviewed: March 10, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.