ACC: Imported MS Excel Spreadsheet May Have Blank Columns

• Microsoft Access versions 2.0, 7.0, 97

SYMPTOMS

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

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.

CAUSE

Microsoft Excel keeps track of all the used cells on a worksheet 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

You can use one of the following methods to avoid the extra columns when you import a Microsoft Excel spreadsheet:

• You can open the table in Design view and delete the extra columns.
• Using Microsoft Excel, you can reset the last cell. For more information about resetting the last cell, please see the following article in the Microsoft Knowledge Base:

        ARTICLE-ID: Q134617
        TITLE     : 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

1. Create a folder (directory) on drive C and name it "Examples" (without the quotation marks).

2. In Microsoft Excel, on the File menu, click New, then click Workbook, and then click OK.

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

4. Save the Workbook to the Examples folder as Test.xls, and then close it.

5. In Microsoft Access, open a new database.

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

7. Save the macro as XlImport, and then close it.

8. Run the XLImport macro.

9. 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."