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

Article ID: 147785

Article Last Modified on 1/19/2007

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 '<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.

MORE INFORMATION

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.

Create the following macro:

      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

Save the macro as XlImport, and then close it.
Run the XLImport macro.
Open the New table. Note that there are 3 fields (F1, F2, and F3), and that the third field is blank.

REFERENCES

For more information about importing data from Microsoft Excel, search the Help Index for "Excel, importing and linking Microsoft Excel data," and then "Import or link data from a spreadsheet."

For more information about the TransferSpreadsheet macro action, search the Help Index for "TransferSpreadsheet action," or ask the Microsoft Access 97 Office Assistant. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

244435 How to reset the last cell in Excel

Additional query words: field f3 doesn t exist in destination table

Keywords: kberrmsg kbinterop kbprb KB147785