Import from Excel to Access

J

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
 
C

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Error while import Excel file to Access 6
Transfering Data from Access Report to Excel. 2
Import TXT file into Excel and keep first "0" on numbers 2
Import from Excel - Text vs Numbers 5
Import Excel to Access Question 5
Lookup Question regarding Data Import to Access Table 1
Import Or Link: Excel 1
import zipcode from excel to access with leading 0 2

Top