Date Formats in Access and Excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.

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.
 
I think you'll need to ask this in a newsgroup related to Excel, since it
would appear that it's Excel that's having the problems.

In general, Access (and doutblessly Excel) expects dates to be in either
mm/dd/yyyy format, or in an unambigous format such as yyyy-mm-dd or
dd-mmm-yyyy. I know, however, that when you're importing dates into either,
you can generally specify the date format.

You might find it interesting to read Allen Browne's "International Dates in
Access" at http://members.iinet.net.au/~allenbrowne/ser-36.html or what I
have in my September 2003 Access Answers column for Pinnacle Publication's
"Smart Access" newsletter. The column and accompanying database can be
downloaded at http://members.rogers.com/douglas.j.steele/SmartAccess.html
 
Back
Top