Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date problems when importing txt files
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date problems when importing txt files
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 "GC" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Importing into excel PROBLEMS | Excel Worksheet Functions | |||
Importing problems | Excel Discussion (Misc queries) | |||
problems with importing an image | Excel Discussion (Misc queries) | |||
importing csv files, problem with date formats | Excel Worksheet Functions | |||
printing/importing problems | Excel Programming |