ACC2: Imported Microsoft Excel Data Contains Wrong Columns
ID: Q120131
|
The information in this article applies to:
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
- Create a new spreadsheet in Microsoft Excel version 3.x or later,
and type any data in cells A1 through AA1.
- Save the file as EXCELTST.XLS and then quit Microsoft Excel.
- Start Microsoft Access and open the sample database NWIND.MDB.
- From the File menu, choose Import.
- In the Data Source box, select the appropriate Microsoft Excel source,
and then choose OK.
- In the File Name box, select the EXCELTST.XLS file you created in
step 2, and then choose the Import button.
- In the Import Spreadsheet Options dialog box, select the Create
New Table option button, and then enter A1:AA1
in the Range box. Choose OK.
- Open the new EXCELTST table in Datasheet view. Note that only 26 columns
(instead of the 27 you specified) were imported.
REFERENCES
Microsoft Access "User's Guide," version 2.0, Chapter 9, "Importing,
Exporting, and Attaching," pages 175-179
Keywords : kbusage IsmExl4
Version : 2.0
Platform : WINDOWS
Issue type : kbbug