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

• Microsoft Access versions 1.0, 1.1
• Microsoft Excel for Windows versions 3.x, 4.x

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.

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          : IntpOff IsmExl4 kberrmsg kbinterop
Version           : 1.0 1.1
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbprb
Solution Type     : kbworkaround