Microsoft KB Archive/165142

= ACC: Cannot Import Numeric Field Names with TransferSpreadsheet =

Article ID: 165142

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q165142



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



SYMPTOMS
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 ''. 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.



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:

<pre class="fixed_text">     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 </li> Save the ImportXL macro.</li> On the Run menu, click Run.</li> Open the NoField table in Datasheet view. Note that the field names are F1, F2, and F3 instead of 1980, 1990, and 2000.</li></ol>

<div class="references_section">