Date problems when importing txt files

[Guest]

Hello,

I have an application that reads data into Access. It accepts different file
types (.txt, .xls), opens them in excel, then manipulates the data and stores
the date in SQL-Server. The files cannot be opened manually (there are lots
of them!), but using the command:

dim booki As Excel.Workbook
set booki = General.Excel.Workbooks.Open(fileName)

Where fileName is the file name provided by the user.

The problem is that when providing a .txt file, Excel reads the date as
mm/dd/yyyy,
while I need it as dd/mm/yyyy. My regional setting is set to dd/mm/yyyy, but
it seems to be ignored.

It is intersting that when I open the .txt files manually, the dates are
formatted correctly (dd/mm/yyyy)!

Does anyone have any idea how to solve the problem? I tried to formatNumber
the date column, but even this does not help!
I tried both on win2000 and xp - and I am using excel 2003 and Acess 2003.

Thanks!
GC.

 

[Sharad Naik]

This is the basic problem with Access. Didn't know that it was problem with
text file too.

In the text file , of course, the date is stored purely as text and has no
format of it's own,
unlike a access table.

One possible way is to use DateSerial Function to swap Day and Month
normal DateSerial function is DateSerial(year, month, day).
When the file is a text file (which you can at run time find out with
Right(fileName, 3)) Then you first get the year, month and day
and in DateSerial feed (year, day, month). E.g:

Dim myDate As Date, myDay As String, myMonth As String, myYear As String
myDate = 'your code to assing the date from the text file
myDay = DatePart("d", myDate)
myMonth = DatePart("m", myDate)
myYear = DatePart("yyyy", myDate)
myDate = DateSerial(myYear, myDay, myMonth)
'Note Day and Month are swapeed in above DateSerial statement.

Now you can assing myDate to a cell in excel or whereever you want to

Also while writing to Access use DateSerial Function as above, if you want
to
make it independent of regional settings.

e.g. in access you want write todays date. Don't use code like
Recordset_Name!Date = Date
instead do as under:
myDate = Date
myDay = DatePart("d", myDate)
myMonth = DatePart("m", myDate)
myYear = DatePart("yyyy", myDate)
Recordset_Name!Date_Field_Name = DateSerial(myYear, myMonth, myDay)
'Note : NO Swapping here !

This will feed the date correctly to the access file irrespective of the
regional date setting
of the machine.

Sharad