Transferspreadsheet turn off autorecognize column type

[SkiGeek]

Hello all,

I'm using some vba to transfer an excel spreadsheet into an existing table. Is there a way to stop the transferspreadsheet command from automatically choosing the datatype? I think this is the problem because when the first few rows of a specific column are blank I get Type Conversion Failure's but if I enter text in the first row the errors go away.

Here is the sub routine I call to transfer the spreadsheet:

'Prereq: First Row must contain column names.
Public Sub ImportExcelSheet(strFilePath As String, strSheet As String, strDestTbl As String)
    DoCmd.SetWarnings False
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strDestTbl, strFilePath, True, strSheet & "!"
    DoCmd.SetWarnings True
    MsgBox "Excel Sheet was imported succesfully"
End Sub

Thank you

 

[SkiGeek]

A bit of a premature post.

Found a solution, well a good description of the problem
http://www.dailydoseofexcel.com/archives/2004/06/03/external-data-mixed-data-types/

But does anyone have any other solutions?