Data Conversion Errors

[Guest]

I’m trying to get data from Excel into Access. I am using Office 2000 so
this may have been fixed in a later version. If it has been fixed let me
know which version fixed this problem. The Excel sheet columns that I have a
problem with are text fields that start with a number ‘0’. They can start
with any other number or letter and everything works as planned. I have
tried doing an import or linking to the Excel sheet directly. The Access
field is also defined as a text field so the data type should not be
changing. If I use the link process the field has ‘#Num!’ notation. If I
import the file, those come across as nulls with an error table being created
that indicates Type Conversion for the error. What am I missing? Is there a
fix?

Thanks for your help!!!

 

[ajetrumpet]

Yes, there is a fix....type the following code into an Excel module and run the macro, then save the file before you import or link it to Access.

Sub Num2Txt()
'paste into an excel module and run in excel
'change the range reference as required
Dim r As Range
For Each r In Range([A1], [C1].End(xlDown))
With r
If IsNumeric(.Value) Then .Value = "'" & .Value
End With
Next
End Sub


This is for Columns A-C. Change accordingly.