format of field on import

[scottian]

I really need some help with importing a spreadsheet. Ive tried formatting a table, Ive set the field to short date and then imported the file, but the excel spreadsheet column contains two different date formats i.e. 17-Feb-07 and 09/06/2007.
When i import with the format set as text it will import this 05/10/2005 as this 38121
And if i set the field as date then anything formatd as 05/10/2005 isnt imported and anything formatted as 17-Feb-07 is altered on import to 17/Feb/07. I cant work this out. Im trying to use this code to format the excel spreadsheet prior to import:-

Function FormatExcellCells()
Dim DBPos, FileDir, FileNameID, stFileName As String
Dim oXL As Object 'Excel.Application
Dim oWb As Object 'Excel.Workbook
Dim oSh As Object 'Excel.Worksheet
DBPos = InStr(CurrentDb.Name, "MDP_Files.mdb")
'FileDir is the path to the folder
FileDir = Left(CurrentDb.Name, DBPos - 1)
FileNameID = ""
FileNameID = Dir(FileDir & "*.xls")
Set oXL = CreateObject("Excel.Application")
Set oWb = oXL.Workbooks.Open(FileDir & FileNameID)
Set oSh = oWb.Sheets("WB_Sheet")
oSh.Columns("E5:E").TextFormat = "dd/mm/yy;@"
oWb.Save
oWb.Close
End Function

but i get an error runtime error 13, type mismatch.

Can anyone help me get the data in or tell me whats wrong with the code above.

cheers


"Children are smarter than any of us. Know how I know that? I don't know one child with a full time job and children."...Bill Hicks

 

[DrSimon]

Scottian
a) How are you importing the spreadsheet into Access? Docmd.transferspreadsheet should cope with this.
b) What is the explicit format of those columns in Excel?
Simon Rouse