Microsoft KB Archive/147785

= Excel spreadsheet that is imported to Access 97 may have blank columns =

Article ID: 147785

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q147785



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



SYMPTOMS
When you import a Microsoft Excel spreadsheet whose cell table goes beyond the last cell that actually contains data, you may get blank columns in the Microsoft Access table. This occurs with the TransferSpreadsheet macro and the Import Menu commands.

When you use a TransferSpreadsheet macro action or the TransferSpreadsheet method in Visual Basic for Applications to append data to an existing table from a spreadsheet file, you may receive the following error message:

Field 'F3' doesn't exist in destination table ' '. Microsoft Access was unable to append it.

The field specified in the error message may vary.



CAUSE
Microsoft Excel keeps track of all the used cells on a worksheet by using an "activecell" table. In some cases, the last cell of that table may refer to a cell outside of the area of the worksheet that is actually being used. When imported into Microsoft Access, all cells of the "activecell" table will be imported; this may result in blank fields.



RESOLUTION
To avoid the extra columns when you import a Microsoft Excel spreadsheet, use one of the following methods:


 * Open the table in Design view and delete the extra columns.
 * Use Microsoft Excel to reset the last cell. For more information about resetting the last cell, please see the following article in the Microsoft Knowledge Base:

134617 XL: Resetting the Last Cell Fixes Memory/Printing Problems
 * Use the TransferSpreadsheet macro action and set the Range argument to the actual range of cells to be imported.



Steps to Reproduce Behavior
 Create a folder (directory) on drive C and name it Examples . In Microsoft Excel, on the File menu, click New, click Workbook, and then click OK.  Enter the following information in Sheet1:

     A1: Sue      B1: 10     C1:  (enter a space in this cell) A2: Tom     B2: 20 A3: Jill    B3: 30 A4: Tim     B4: 40  Save the Workbook to the Examples folder as Test.xls, and then close it. In Microsoft Access, open a new database.</li>  Create the following macro:

<pre class="fixed_text">     Macro Name     Action --     Test1          TransferSpreadsheet

Test1 Actions Transfer Type:   Import Spreadsheet Type: Microsoft Excel 97 (or Microsoft Excel 5-7, if                       appropriate) Table Name:      New File Name:       C:\Examples\Test.xls Has Field Names: No                        </li> Save the macro as XlImport, and then close it.</li> Run the XLImport macro.</li> Open the New table. Note that there are 3 fields (F1, F2, and F3), and that the third field is blank.</li></ol>

<div class="references_section">