ACC2000: Can't Import Numeric Field Names with TransferSpreadsheet

ID: Q197525

The information in this article applies to:

Microsoft Access 2000

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

SYMPTOMS

When you use the TransferSpreadsheet macro action or TransferSpreadsheet 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 the TransferSpreadsheet action or method 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. 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:

' 1980' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.

Method 2: Use the Import Spreadsheet Wizard

To use the Import Spreadsheet Wizard, follow these steps:

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 the TransferSpreadsheet action or method, click Microsoft Access Help on the Help menu, type "transferspreadsheet action" in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

To view the "TransferSpreadsheet method" topic, click "See Also," and in the Topics Found dialog box, click TransferSpreadsheet method.

Additional query words: fieldname

Keywords : kbdta IsmOthr IsmExl5
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbprb