ACC2000: #Num Appears in Linked Microsoft Excel Spreadsheet (208414)


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q208414
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).

SYMPTOMS

When you link to a Microsoft Excel spreadsheet from a Microsoft Access database, and the fields in the spreadsheet contain both numbers and text, the fields that contain text appear as #Num! in the linked table in Microsoft Access.

CAUSE

Access assigns the data type for each field based on data contained within the first eight rows it links. For example, if a field that contains mostly text values has a number in the first eight rows, Microsoft Access assigns the Number data type, and then cannot link the rest of the records. Any format that you apply to the fields in Microsoft Excel will be ignored by Microsoft Access when the spreadsheet is linked. NOTE: When you import rather than link an Excel spreadsheet, the Text data type has a priority within the import algorithm. For example, if the predominant data type based on a sampling of data is numeric, but there is at least one text value within that sample, Access will import the entire field as Text.

RESOLUTION

Before you link to the Excel spreadsheet from Access, make sure that the spreadsheet has the same type of data in each field (column) and the same fields in every row.

-or-

Use the following procedure to format the cells in the spreadsheet so that they will appear correctly in Access.
  1. Open the spreadsheet in Microsoft Excel
  2. Format the cells in the spreadsheet that contain mixed data as Text. You should do this from the Format menu in Microsoft Excel.
  3. Create a macro in Microsoft Excel that contains the following procedure:
    Sub Addspace()

   Dim cell As Object

   For Each cell In Selection
      cell.Value = " " & cell.Value
      cell.Value = Right(cell.Value, Len(cell.Value) - 1)
   Next
   
End Sub
  4. Highlight the cells in the spreadsheet that contain the mixed data.
  5. Run the macro, and then save the spreadsheet.
  6. Open your database in Access.
  7. Link to the spreadsheet that you created in Excel. Note your data is now in the correct format.

MORE INFORMATION

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

Steps to Reproduce Behavior

  1. Start Microsoft Excel and create a new spreadsheet.
  2. Type the following in cells A1 through A5:
       A1: 12345
   A2: 12345
   A3: 12345
   A4: 12345
   A5: 12345A
  3. Save the spreadsheet as Book1, and then close Microsoft Excel.
  4. In Microsoft Access, open the sample database Northwind.mdb.
  5. On the File menu, point to Get External Data, and then click Link Tables.
  6. In the Link box, click Microsoft Excel in the Files Of Type list, and then select the Book1 spreadsheet that you created. Click Link.
  7. In the Link Spreadsheet Wizard, click Next twice, accept Sheet1 for the Linked Table Name, and then click Finish.
  8. Click OK in the Link Spreadsheet Wizard message box.
  9. Open the linked table (Sheet1). Note the #Num! in the last record.

REFERENCES

For more information about linking data, click Microsoft Access Help on the Help menu, type Link data from a spreadsheet in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
Modification Type:MinorLast Reviewed:7/16/2004
Keywords:kbinterop kbprb KB208414
©2004 Microsoft Corporation. All rights reserved.