Run-Time Error '3421': How do I fix it?

[BTU_needs_assistance_43]

I'm running code to import columns of information from Excel to an Access
table and the program gets far enough to open the Excel file in a new window
but then i get this '3421' error "Data Type Conversion Error" message. I've
checked the source Excel cells and matched them to the same type of table
cells in Access (ex: number to number, text to text, etc...). The range is
set to start at the first cell that contains data. The table, file,
worksheet, and database are all right. What am I doing wrong?
_____________________________________________________________________

Private Sub Command6_Click()
Dim lngColumn As Long
Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim blnEXCEL As Boolean
Dim strLocation As String

blnEXCEL = True

On Error Resume Next
Set xlx = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set xlx = CreateObject("Excel.Application")
blnEXCEL = True
End If
Err.Clear
On Error GoTo 0

xlx.Visible = True

Set xlw = xlx.Workbooks.Open(C:\My Documents\Excel.xls, , True)

Set xls = xlw.Worksheets("Cast")

Set xlc = xls.Range("B5") ' this is the first cell that contains data

Set dbs = CurrentDb()

Set rst = dbs.OpenRecordset("Cast Reports", dbOpenDynaset, dbAppendOnly)

Do While xlc.Value <> ""
rst.AddNew
For lngColumn = 0 To rst.Fields.Count - 1
rst.Fields(lngColumn).Value = xlc.Offset(0, lngColumn).Value
Next lngColumn
rst.Update
Set xlc = xlc.Offset(1, 0)
Loop

rst.Close
Set rst = Nothing

dbs.Close
Set dbs = Nothing

Set xlc = Nothing
Set xls = Nothing
xlw.Close False
Set xlw = Nothing
If blnEXCEL = True Then xlx.Quit
Set xlx = Nothing

End Sub

 

[JimBurke via AccessMonster.com]

When I've had a problem like that it was due to an alphanumeric field. I
think that Access determines whether a column is numeric or not based on what
it sees in the first row (or some number of rows) it encounters. So if you
have a column that has alphanumeric values, with some consisting of all
numbers, and the first row has a value that is all numbers, it considers that
to be a numeric field. Then, when it gets to an alphanumeric field you end up
with a conversion error. My solution was to move to the alphanumeric fields
to the first rows - I did this because I have a manual process I have to go
through anyway with the spreadsheet, so it' simple enough for me to do that
in that process. This causes Access to interpret the field as Text since the
first value it sees is alphanumeric. Even if you have the column in the table
defined as text, Access still does this for some reason (from what I remember,
it's been a while). I think the only other way to prevent this is to go into
the spreadsheet and define the column as a text field by formatting it -
Excel then appends a ' to the front of each value, which you only see when
viewing it in the formula bar at the top. If this is what is happening in
your case, I'm not sure there's a way around this that doesn't involve some
sort of manual intervention, either by moving rows around or formatting the
column. Maybe someone else here knows of a simpler solution.

 

[BTU_needs_assistance_43]

I really really REALLY hope you're wrong because there are hundred of files
and the program I'm setting up will not only be used to add them but future
files too and they all come off of a consistent template. I might try and
move the starting fields up a column to include the headers in the
spreadsheets to set them all to text fields but that would require however
many more lines of code so that my program would go back and delete those
after all the data has been imported... this is gonna suck!

 

[JimBurke via AccessMonster.com]

One way to test it quickly. First look at the spreadsheet and column that is
giving you the error. See if the first value or set of values is numeric. If
so, and there are other values below that are alphanumeric, you probably have
the situation I described. Do a 'manual' import of that spreadhseet you are
getting that error with (use File, Get Exernal Data, Import). See if you get
the conversion error. Then go into the spreadhseet and make the value for
that column in the first row non-numeric (just add a letter to the front of
it). Then try another manual import and see if the data conversion error goes
away.

I really really REALLY hope you're wrong because there are hundred of files
and the program I'm setting up will not only be used to add them but future
files too and they all come off of a consistent template. I might try and
move the starting fields up a column to include the headers in the
spreadsheets to set them all to text fields but that would require however
many more lines of code so that my program would go back and delete those
after all the data has been imported... this is gonna suck!

When I've had a problem like that it was due to an alphanumeric field. I
think that Access determines whether a column is numeric or not based on what

[quoted text clipped - 79 lines]

 

[JimBurke via AccessMonster.com]

FYI, here's a link to the Microsoft site documenting this problem.
Unfortuantely, their solution is to add a dummy first row.

http://support.microsoft.com/kb/109376

I really really REALLY hope you're wrong because there are hundred of files
and the program I'm setting up will not only be used to add them but future
files too and they all come off of a consistent template. I might try and
move the starting fields up a column to include the headers in the
spreadsheets to set them all to text fields but that would require however
many more lines of code so that my program would go back and delete those
after all the data has been imported... this is gonna suck!

When I've had a problem like that it was due to an alphanumeric field. I
think that Access determines whether a column is numeric or not based on what

[quoted text clipped - 79 lines]

 

[JimBurke via AccessMonster.com]

One more reply! You could automate adding a new row. You can open Excel from
VBA and manipulate spreadsheets, so if you had to you could make that part of
your process - before importing the file, open it and add the new row, close
it and close Excel, then do the import. Then after the import, delete the
'dummy' first row from the table. I'm sure it seems like a hassle, but once
you have the code developed it'll work from there on out and there wouldn't
be any manual intervention needed. You also may want to repost here to see if
anyone has a simpler solution. It seems like Microsoft would have an easy
workaround for situations like this, some sort of import parameter or
something, but apparently they don't, at least from what I've seen.

I really really REALLY hope you're wrong because there are hundred of files
and the program I'm setting up will not only be used to add them but future
files too and they all come off of a consistent template. I might try and
move the starting fields up a column to include the headers in the
spreadsheets to set them all to text fields but that would require however
many more lines of code so that my program would go back and delete those
after all the data has been imported... this is gonna suck!

When I've had a problem like that it was due to an alphanumeric field. I
think that Access determines whether a column is numeric or not based on what

[quoted text clipped - 79 lines]