Does *Anyone* know how to....

[kirkm]

I'm desperate! Have spend hundreds of hours
trying to resolve this issue.

How can you import (or Link) from Excel into Access
without getting a million "#NUM!" in place of the data???

I've read countless articles, theories, codswallop
no mortal could hope to understand and all I achieve is to see more of
them permeate through my data. I could happily
throw the dam computer out the window and go back to
reliable pen and paper.

But is there any hope - does anyone know the fix for this - and
explain it??

Or is this a bug with no solution ? Just knowing that for sure would
help.

Thanks - Kirk

 

[Guest]

See if this helps: http://www.computing.net/office/wwwboard/forum/4469.html

 

[Guest]

The following is from the website I previously referenced:
-------------------------------------------------------------------------

Microsoft has the resolution for your issue. It's the same
one I had and I found this.
Article ID : 162539
Last Review : November 14, 2003
Revision : 1.0
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 following procedure 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,
follow these steps:
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()

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

 

[Dirk Van de moortel]

I'm desperate! Have spend hundreds of hours
trying to resolve this issue.

How can you import (or Link) from Excel into Access
without getting a million "#NUM!" in place of the data???

I've read countless articles, theories, codswallop
no mortal could hope to understand and all I achieve is to see more of
them permeate through my data. I could happily
throw the dam computer out the window and go back to
reliable pen and paper.

But is there any hope - does anyone know the fix for this - and
explain it??

Or is this a bug with no solution ? Just knowing that for sure would
help.

Thanks - Kirk

Google http://www.google.com/search?q=#num+access+excel
First hit, response Number 5, points to Microsoft Article ID 162539
Then Google http://www.google.com/search?q=kb162539
First hit: http://support.microsoft.com/kb/162539/EN-US/

Dirk Vdm