Bug in OLEDB ?

R

Ric

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.
 
K

keepitcool

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 >
 
O

onedaywhen

...
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.
Click to expand...

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/d.../en-us/office97/html/workingwithtextfiles.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/default.asp?url=/library/en-us/dnclinic/html/scripting03092004.asp
Someone has
suggested that this is a bug and the solution is to go
back to using DDE
Click to expand...

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.

--
 
G

Guest

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.
 
K

keepitcool

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 >
 
Top