ACC: #Num Appears in Linked Microsoft Excel Spreadsheet

• Microsoft Access versions 7.0, 97

SYMPTOMS

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

When you link to a Microsoft Excel 7.0 or 97 spreadsheet from a Microsoft Access 7.0 or 97 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

Microsoft Access assigns the data type for each field based on the data in the first row it links. For example, if a field that contains mostly text values has a number in the first row, 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.

RESOLUTION

Before you link to the spreadsheet in Microsoft 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 procedure below to format the cells in the spreadsheet so that they will appear correctly in Microsoft Access.

Formatting the Cells in the Microsoft Excel Spreadsheet

To format the cells in the spreadsheet so that the linked table data appears correctly in Microsoft Access, do the following:

1. Open the spreadsheet in Microsoft Excel 7.0 or 97.

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()
   
         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 Microsoft Access 7.0 or 97.

7. Link to the spreadsheet that you created in Microsoft Excel. Note that your data is now in the correct format.

MORE INFORMATION

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. Open the sample database Northwind.mdb in Microsoft Access.

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 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, search the Help Index for "linked tables, linking table data" or ask the Microsoft Access 97 Office Assistant.

Keywords          : kbinterop
Version           : 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbprb
Solution Type     : Info_Provided