Thread: Type Conversion Error while importing excel sheet VB

Hello,

I have a VB code which grabs an excel2003 sheet and imports it into a table in my access 2007 database. My problem is when I import the sheet, 2 columns are giving me the type conversion error because my data has mixed alpha only / alpha numeric / and numeric only.

I believe the import is looking at the first record, seeing alpha and then erroring all of the numeric only records. How can I fix this? I can't alter the spreadsheet as I receive it daily, and the whole point of my VB was to save me time. Any suggestions?

Private Sub Command36_Click()
DoCmd.SetWarnings False
Dim ImportDate As String
ImportDate = InputBox("Please enter load date")

Set db = CurrentDb
Set rs = db.openrecordset("UploadDate")

With rs
'rs.Edit
rs.AddNew
rs!UploadDate = ImportDate
rs.Update
rs.Close
End With

DoCmd.OpenQuery ("Qry_Delete_TBL_RepRoster_Temp")
DoCmd.OpenQuery ("Qry_Delete_TBL_RepRoster")
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TBL_RepRoster_Temp", "S:\Magenta\Sanofi Pasteur\Sales Lead Program\Rep Rosters Archive\Field Roster " & ImportDate & ".xls", True, "A:V"
DoCmd.OpenQuery ("Qry_Append TBL_RepRoster_Temp_to_TBL_Rep_Roster")
Uploadtxtbox = " The last uploaded RepRoster was: " & ImportDate
MsgBox ("RepRoster Successfully Updated!")
DoCmd.SetWarnings True
End Sub