Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Addin altering date format on text file import
Hi,
I have an Excel addin (written in VBA) which includes the following line in a function to open files; Excel.Application.Dialogs(xlDialogOpen).Show I am opening a text file which is a list of tab-delimited pairs like; 1 1/3/04 2 2/5/04 3 5/8/04 These dates are ambiguous, in that they are valid dates in either DMY format or MDY format. My computer's Regional Settings are setup for DMY format, and in Excel normally, opening this file imports the dates to cells as-is (in DMY format). However, with my addin installed, and nothing but the earlier line of code involved, Excel changes those dates so they are read as MDY formatted. (The difference can be seen by changing the format of the cells after import to some non-ambiguous format, e.g; Wednesday, 1st March, 2004) Any idea why importing this file in an addin context causes the dates to be formatted unnecessarily? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Addin altering date format on text file import
would it help to add a date format to your code as the data is imported?
"RJ Lohan" wrote: Hi, I have an Excel addin (written in VBA) which includes the following line in a function to open files; Excel.Application.Dialogs(xlDialogOpen).Show I am opening a text file which is a list of tab-delimited pairs like; 1 1/3/04 2 2/5/04 3 5/8/04 These dates are ambiguous, in that they are valid dates in either DMY format or MDY format. My computer's Regional Settings are setup for DMY format, and in Excel normally, opening this file imports the dates to cells as-is (in DMY format). However, with my addin installed, and nothing but the earlier line of code involved, Excel changes those dates so they are read as MDY formatted. (The difference can be seen by changing the format of the cells after import to some non-ambiguous format, e.g; Wednesday, 1st March, 2004) Any idea why importing this file in an addin context causes the dates to be formatted unnecessarily? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Addin altering date format on text file import
If it goes through VBA, and it does in this case, then the default
interpretation is US English date format: MDY Look at application.GetOpenfileName to get the name of the file to open. then, if the file extension is not CSV, you can use opentext to open the file with the info array defining how to interpret the dates (assume they will all have the same layout). If the file has a CSV extension, excel ignores the settings in opentext. or If you are using xl2003, you can see if making settings under tools=options=International helps rectify the problem. -- Regards, Tom Ogilvy "RJ Lohan" wrote in message ... Hi, I have an Excel addin (written in VBA) which includes the following line in a function to open files; Excel.Application.Dialogs(xlDialogOpen).Show I am opening a text file which is a list of tab-delimited pairs like; 1 1/3/04 2 2/5/04 3 5/8/04 These dates are ambiguous, in that they are valid dates in either DMY format or MDY format. My computer's Regional Settings are setup for DMY format, and in Excel normally, opening this file imports the dates to cells as-is (in DMY format). However, with my addin installed, and nothing but the earlier line of code involved, Excel changes those dates so they are read as MDY formatted. (The difference can be seen by changing the format of the cells after import to some non-ambiguous format, e.g; Wednesday, 1st March, 2004) Any idea why importing this file in an addin context causes the dates to be formatted unnecessarily? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Addin altering date format on text file import
My code isn't actually doing the importing, it's just Excel's normal routine
when doing an open on a delimited text file. The only code I have involved is this; Excel.Application.Dialogs(xlDialogOpen).Show to launch an 'Open' dialog. From then on, it's all Excel... "gocush" wrote: would it help to add a date format to your code as the data is imported? "RJ Lohan" wrote: Hi, I have an Excel addin (written in VBA) which includes the following line in a function to open files; Excel.Application.Dialogs(xlDialogOpen).Show I am opening a text file which is a list of tab-delimited pairs like; 1 1/3/04 2 2/5/04 3 5/8/04 These dates are ambiguous, in that they are valid dates in either DMY format or MDY format. My computer's Regional Settings are setup for DMY format, and in Excel normally, opening this file imports the dates to cells as-is (in DMY format). However, with my addin installed, and nothing but the earlier line of code involved, Excel changes those dates so they are read as MDY formatted. (The difference can be seen by changing the format of the cells after import to some non-ambiguous format, e.g; Wednesday, 1st March, 2004) Any idea why importing this file in an addin context causes the dates to be formatted unnecessarily? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date conversion on text file import | Excel Discussion (Misc queries) | |||
Please help me with altering text in excel | New Users to Excel | |||
Cell format with Data Import (date appearing as text) | Excel Discussion (Misc queries) | |||
Date format not correct when you convert a CSV text file in Excel | Excel Discussion (Misc queries) | |||
Import text file into excel with preset file layout, delimeters VBA | Excel Programming |