This is driving us crazy. It appears that when we use OLE
to open a csv in Excel, all dates are being interpreted as
US format, despite the regional settings being set to UK.
If we open the csv manually then all is fine. Someone has
suggested that this is a bug and the solution is to go
back to using DDE, but I cannot find any information about
it. Can somebody please confirm one way or the other and
maybe offer a solution ? We are using VB6.

Ric,

In int'l environments you'll need to avoid the CSV driver like the plague
CSV is a pesky and VERY badly written converter.

When you rename the .csv file to .txt, and THEN open it you have far more
control.


If you program for Excel XP+ only have a look at the workbooks.OpenTEXT
method.

You can also force a standard text open and then use the TextToColumn
wizard dialog.

I'm no longer a frequent visitor to the ng, pls email if questions
adres below


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Ric" wrote:

This is driving us crazy. It appears that when we use OLE
to open a csv in Excel, all dates are being interpreted as
US format, despite the regional settings being set to UK.
If we open the csv manually then all is fine. Someone has
suggested that this is a bug and the solution is to go
back to using DDE, but I cannot find any information about
it. Can somebody please confirm one way or the other and
maybe offer a solution ? We are using VB6.

"Ric" wrote ...

when we use OLE
to open a csv in Excel, all dates are being interpreted as
US format, despite the regional settings being set to UK.

Are you using a Schema.ini file, knowingly or otherwise (look in your
csv file's folder)? You probably should be using one to explicitly
specify columns' data types because it sounds like your dates are
being imported as text. Take a look at:

Working with Text Files
http://msdn.microsoft.com/library/de...htextfiles.asp

"A Schema.ini file is ... recommended when your text file contains
Date/Time, Currency, or numeric floating-point data, or any time you
want more control over handling the data in the text file."

This article, despite the cringe-worthy pun, could also be useful:

Much ADO About Text Files
http://msdn.microsoft.com/library/de...ng03092004.asp

Someone has
suggested that this is a bug and the solution is to go
back to using DDE

To employ the analogy of aeroplanes (that's 'airplanes' to
non-residents), if ADO is a jet engine (not to be confused with the
Jet SQL engine) then DDE is a turbo prop and should be consigned to
history, although there are a lot of planes out there still using them
(perhaps I should've said 'elastic band' except they are useful for
other things). So ignore your ground crew and keep full steam ahead
with your OLE DB proto plane.

--

Thanks for the pointer. I'm now using the OpenText method.
The weird thing is that it ignores my date formatting
unless I explicitly set the formatting for every column.
Oh well, at least it works now.

Ric.
-----Original Message-----
Ric,

In int'l environments you'll need to avoid the CSV driver
like the plague
CSV is a pesky and VERY badly written converter.

When you rename the .csv file to .txt, and THEN open it
you have far more
control.


If you program for Excel XP+ only have a look at the
workbooks.OpenTEXT
method.

You can also force a standard text open and then use the
TextToColumn
wizard dialog.

I'm no longer a frequent visitor to the ng, pls email if
questions
adres below


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Ric" wrote:

This is driving us crazy. It appears that when we use
OLE
to open a csv in Excel, all dates are being interpreted
as
US format, despite the regional settings being set to
UK.
If we open the csv manually then all is fine. Someone
has
suggested that this is a bug and the solution is to go
back to using DDE, but I cannot find any information
about
it. Can somebody please confirm one way or the other
and
maybe offer a solution ? We are using VB6.


.

If you and your users use xlXP+ only,
have a look at the LAST argument (Local) in the OpenText Method.
(it's undocumented.. but it seems to do the trick!)

Experiment with macrorecorder...


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


wrote:

Thanks for the pointer. I'm now using the OpenText method.
The weird thing is that it ignores my date formatting
unless I explicitly set the formatting for every column.
Oh well, at least it works now.

Ric.
-----Original Message-----
Ric,

In int'l environments you'll need to avoid the CSV driver
like the plague
CSV is a pesky and VERY badly written converter.

When you rename the .csv file to .txt, and THEN open it
you have far more
control.


If you program for Excel XP+ only have a look at the
workbooks.OpenTEXT
method.

You can also force a standard text open and then use the
TextToColumn
wizard dialog.

I'm no longer a frequent visitor to the ng, pls email if
questions
adres below


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Ric" wrote:

This is driving us crazy. It appears that when we use
OLE
to open a csv in Excel, all dates are being interpreted
as
US format, despite the regional settings being set to
UK.
If we open the csv manually then all is fine. Someone
has
suggested that this is a bug and the solution is to go
back to using DDE, but I cannot find any information
about
it. Can somebody please confirm one way or the other
and
maybe offer a solution ? We are using VB6.


.