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

  • Thread starter BTU_needs_assistance_43
  • Start date


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
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 <> ""
For lngColumn = 0 To rst.Fields.Count - 1
rst.Fields(lngColumn).Value = xlc.Offset(0, lngColumn).Value
Next lngColumn
Set xlc = xlc.Offset(1, 0)

Set rst = Nothing

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


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!

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