Excel Addin altering date format on text file import

[RJ Lohan]

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?

[gocush]

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?

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
> 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?

[RJ Lohan]

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?