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 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:
  
  Q134617 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.



2. In Microsoft Excel, on the File menu, click New, 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."

For more information about the TransferSpreadsheet macro action, search the Help Index for "TransferSpreadsheet action," or ask the Microsoft Access 97 Office Assistant.

Keywords          : kberrmsg kbinterop IntpOff 
Version           : 2.0 7.0 97
Platform          : WINDOWS 
Issue type        : kbprb 

Last Reviewed: April 23, 1999