Import from excell

[Twanne]

Hi,

I've got these files in excell that need to be imported to access. Now I've got one file, and when I try to import it, it acts weird. There are two fields in the excell fiile (Start, Stop) that are dates. When I have done my import the dates change to some number for some reason. 19/02/2007 changes into 39132. I've got an idea why it is done (dates are stored as long in access) but I got no idea how to get around this. This only happens to that format of date that needs to be imported. When I got a full date with time and all it acts normal.

Could anybody help me with this??

Greetz
Twanne

The trees don't fall far from the apple.

[Twanne]

Owkay, never mind this. Apparently when you have something in a field you want to import from excell you cant let other (like plain text or in my case " - - ") values than the standard in the field. Access wil try to interpret this it's own way and screw up your data.

Greetz
Twanne

Throw the apple far enough.

[GuillermoDG]

If you import from Acccess with the option in the Access menu, my answer will not help you.
I import using programs (VB 2005 specifically). The basic idea is:

Dim AppExcel As Excel.Application
AppExcel = New Excel.Application
AppExcel.Visible = False

Try
AppExcel.Workbooks.Open(Filename:=sFileName)
Catch ex As Exception
MessageBox.Show("Unable to open file: " + sFileName, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Return
End Try

Dim sCell, sTitle As String
Dim objTitle As Object
Dim iRow As Integer
iRow = 2
Do
sCell = "B" + CStr(iRow)
AppExcel.Range(sCell).Select()
objTitle = AppExcel.Selection.Value

On the object, or variant, you can do the validations that you want

If objTitle Is Nothing Then Exit Do
sTitle = Trim(CStr(objTitle))
If sTitle = "" Then Exit Do
sCell = "A" + CStr(iRow)
AppExcel.Range(sCell).Select()

Here prepare and execute an INSERT command

iRow = iRow + 1
Loop
AppExcel.Workbooks.Close()

AppExcel.Quit()