Import from Excel to Access

[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 the 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

 

[Clif McIrvin]

Is there a reason you're using code instead of linking or importing the
spreadsheet? Let Access do the work for you!

That being said, I notice that
rs.Fields(i).Value = XLrs.Fields(i).Value
could be doing implicit datatype conversions for you.


If rs.Fields(i) is not a text field you're out of luck.
Even if the cell in the spreadsheet is text, if XLrs.Fields(i) is
numeric; again you're hosed.