I will need to import regurlarly some data (Excel Sheet) in an access table.
The problem I have is that the format (type) does not always match and can lead to errors (especially with dates).
So I decided to go through each column in excel and set the correct format like so (VBA in Access module):
Sub FormatFile(FileName As String)
Dim ExcelApp As New Excel.Application
Dim ExcelWST As New Excel.Worksheet
Dim ExcelRNG As Excel.Range
Dim myDB As Database
Dim recType As DAO.Recordset
Set myDB = CurrentDb
'open Excel file
ExcelApp.Workbooks.Open FileName
Set ExcelWST = ExcelApp.ActiveSheet
'in table ExcelFileFormat I stored the type of field for each row
Set recType = myDB.OpenRecordset("select * from Excelfileformat order by fieldorder")
If recType.EOF And recType.BOF Then
Else
'go through all columns and set the correct format
While Not recType.EOF
ExcelWST.Columns(recType!fieldorder).NumberFormat = recType!FieldType
recType.MoveNext
Wend
End If
ExcelApp.Workbooks.Close
ExcelApp.Quit
Set recType = Nothing
Set ExcelWST = Nothing
Set ExcelApp = Nothing
Set myDB = Nothing
End Sub
The procedure seems fine, no errors etc...
BUT when I open my excel file, some fields are OK, but not all : Dates : I have a column with date that are in format : dd-mon-yy (it is actually in the "general" format, but looks like dd-mon-yy for example 10-SEP-04)
After I ran the procedure the cell format is "dd/mm/yyyy" as desired, but it still looks like "dd-mon-yy")
Problem is I can't import this column in a date field in access ...
Any suggestions ?
