ACC2: Imported Microsoft Excel Data Contains Wrong Columns

• Microsoft Access version 2.0

SYMPTOMS

Novice: Requires knowledge of the user interface on single-user computers.

When you are importing data from a Microsoft Excel spreadsheet, Microsoft Access may import the wrong data if the specified range extends beyond column Z (the 26th column), or if the specified range begins after column Z.

CAUSE

Microsoft Access miscounts columns by one after the 26th column. For example, if you specify an import range of A1:AD10 (30 columns and 10 rows), the actual range that will be imported is A1:AC10 (29 columns and 10 rows).

If the import range begins with a column beyond column Z, the first column imported will be one column to the left of the specified column. For example, if you specify an import range of AF1:BB4 (23 columns and 4 rows), the actual range that will be imported is AE1:BA4 (23 columns and 4 rows).

RESOLUTION

There are three workarounds for this problem:

• Create a named range in Microsoft Excel that includes all the cells you want to import, and then use that named range to import the data into Microsoft Access.
• Add an extra column to the range you specify when you are importing the data. For example, if you want to import the 27 columns in the range A1:AA1, specify the range A1:AB1.
• If the range you want to import begins after column Z, offset the column range by one. For example, if you want to import the range AF1:BB1, specify the range AG1:BC1.

STATUS

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

MORE INFORMATION

Steps to Reproduce Problem

1. Create a new spreadsheet in Microsoft Excel version 3.x or later, and type any data in cells A1 through AA1.

2. Save the file as EXCELTST.XLS and then quit Microsoft Excel.

3. Start Microsoft Access and open the sample database NWIND.MDB.

4. From the File menu, choose Import.

5. In the Data Source box, select the appropriate Microsoft Excel source, and then choose OK.

6. In the File Name box, select the EXCELTST.XLS file you created in step 2, and then choose the Import button.

7. In the Import Spreadsheet Options dialog box, select the Create New Table option button, and then enter "A1:AA1" (without quotation marks) in the Range box. Choose OK.

8. Open the new EXCELTST table in Datasheet view. Note that only 26 columns (instead of the 27 you specified) were imported.

REFERENCES