Import from Excel to Access does not get text with leading zero

[JP]

I am importing data from Excel into Access. I am having difficulty
importing text entries with a leading zero. For example, the Excel file has
some columns with mixed numeric and text values. 1234 is entered as a
number and 0123 is entered as text to preserve teh leading zero.

My code below only imports the 1234 into teh text field and does not pickup
the 0123 which is entered in Excel with a leading apostrophy.

Is there a better method that I can use than my code below? I am open to
suggestions.



Do Until XLrs.EOF = True
rs.AddNew
lngRow = lngRow + 1
For i = 0 To XLrs.Fields.Count - 1
If Not IsNull(XLrs.Fields(i)) Then
strField = rs.Fields(i).Name
rs.Fields(i).Value = XLrs.Fields(i).Value
End If
Next i
rs.Update
XLrs.MoveNext
Loop

 

[Ken Snell MVP]

See this article:

Avoid DataType Mismatch Errors when Importing Data from an EXCEL File or
when Linking to an EXCEL File
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#DataTypeErr

Although it doesn't mention the dropping of leading zeroes, the cause for
that is because ACCESS sees the value in the EXCEL cell as a number, not as
a text string.

 

[JimBurke via AccessMonster.com]

#4 in Ken's list of things you can do to fix this is what I would do. I'm
assuming that you're letting Access define the table. It is likely assuming
that those values are numeric and creating that field as a numeric field
rather than a text field. If you define the table ahead of time (just do this
yourself manually, no need to do it in code) and define that field as text
that should take care of it. Just make sure that the table is empty before
you do the import - run a delete query against it to delete any existing rows,
e.g. DoCmd.RunSQL "DELETE * FROM yourTableName"