ACC: Can't Import Numeric Field Names with TransferSpreadsheet

• Microsoft Access versions 7.0, 97

SYMPTOMS

Moderate: Requires basic macro, coding, and interoperability skills.

When you use the TransferSpreadsheet macro action or Visual Basic method to import a Microsoft Excel spreadsheet that contains numeric field names, the fields in the new table are named F1, F2, F3, and so on.

RESOLUTION

There are two ways you can work around this behavior.

Method 1: Change Field Names in Microsoft Excel

If you want to use TransferSpreadsheet to import a Microsoft Excel file that contains field names, you must change the field names in Microsoft Excel so they are non-numeric. It is not enough to format the cells as text in Microsoft Excel, but leave the numeric values. You must change the field names or precede the numbers with one or more non-numeric characters, such as a letter of the alphabet or an underscore (_).

NOTE: You cannot use a space as the first character of the field name. If you format the field name as Text in Microsoft Excel, and then you use a space as the first character of the field name in an attempt to preserve its numeric name, you receive the following error message when you try to import the file:

   The database engine can't find '<Field Name>'. Make sure it is a valid
   parameter or alias name, that it doesn't include invalid characters or
   punctuation, and that the name isn't too long.

Method 2: Use the Import Spreadsheet Wizard

1. On the File menu, point to Get External Data, and then click Import.

2. In the Import dialog box, select Microsoft Excel (*.xls) in the Files Of Type box, and then select your spreadsheet file. Click Import.

3. Click Next on the first screen of the Import Spreadsheet Wizard.

4. Click "First Row Contains Column Headings" on the second screen of the wizard, and then click Next.

5. Follow the instructions on the remaining screens of the wizard to import your spreadsheet file.

MORE INFORMATION

Steps to Reproduce Behavior

The following example uses the TransferSpreadsheet action in a macro. You can also reproduce this behavior if you use the TransferSpreadsheet method of the DoCmd object in Visual Basic code.

1. Start Microsoft Excel and create a new workbook.

2. Type the following three rows of data in the spreadsheet:

         A      B      C
         ------------------
         1980   1990   2000
         a      b      c
         d      e      f
   
   

3. Save the spreadsheet as C:\My Documents\NoField.xls, and then quit Microsoft Excel.

4. Start Microsoft Access and open the sample database Northwind.mdb.

5. Create a new macro called ImportXL:

         Macro Name: ImportXL
         --------------------
         TransferSpreadsheet
   

         ImportXL Actions
         -----------------------------------------------------------------
         TransferSpreadsheet
            Transfer Type: Import
            Spreadsheet Type: <Select the correct Microsoft Excel version>
            Table Name: NoField
            File Name: C:\My Documents\NoField.xls
            Has Field Names: Yes
   
   

6. Save the ImportXL macro.

7. On the Run menu, click Run.

8. Open the NoField table in Datasheet view. Note that the field names are F1, F2, and F3 instead of 1980, 1990, and 2000.

REFERENCES

For more information about using TransferSpreadsheet in a macro or in code, search the Help Index for "TransferSpreadsheet action" or "TransferSpreadsheet method."