ACC: Can't Import Numeric Field Names with TransferSpreadsheet

ID: Q165142

The information in this article applies to:

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

Use either of the following methods to 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 numeric field names, you must change the field names in Microsoft Excel so that 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

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

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

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

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

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.

Start Microsoft Excel and create a new workbook.

Type the following three rows of data in the spreadsheet:


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

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

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

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

Save the ImportXL macro.

On the Run menu, click Run.

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

Additional query words: fieldname

Keywords : kbusage IsmOthr IsmExl5
Version : 7.0 97
Platform : WINDOWS
Issue type : kbprb