CSV File - Date Formats

[Nigel]

Hi All
I have a CSV file that contains dates held in European format (dd/mm/yyyy).
When I open it manually using Excel, the dates remain unchanged. If I open
it using VBA then the dates are changed to US format where they can... so
01/04/2005 becomes 04/01/2005. However dates that cannot be converted eg
23/03/2005 are left as is. I end up with a mixture of dates and formatting
does reset my dates to the correct format!

How can I avoid this problem?

 

[Rich_z]

CSV files have a specification.....! (Yup...)

One of the things it states is that dates should be in the format
YYYYMMDD. It's then up to the receiving application to reformat these
as appropriate.

If you can, get the output changed to conform to the specification,
otherwise rather than input them into a date variable, enter them into
a string and then convert that to a date which will give you control
over the format of the date.

 

[Andibevan]

Nigel,

Without seeing your code it is difficult to comment but you could adapt the
following for your code so that each extracted date is re-formatted as in
the second line of this code.


MyVar = "12/13/05"
Range("A1").Value = Format(MyVar, "dd/mm/yy")

HTH

Andi

 

[Tom Ogilvy]

I have US English, so I don't personally have to deal with this problem, but
here is my understanding:


In Excel 2002/2003, there are some options you can use to control this.

expression.Open(FileName, UpdateLinks, ReadOnly, Format, Password,
WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter, Editable,
Notify, Converter, AddToMru, Local, CorruptLoad)

help on Local:
Local Optional Variant. True saves files against the language of Microsoft
Excel (including control panel settings). False (default) saves files
against the language of Visual Basic for Applications (VBA) (which is
typically US English)


In other versions, you can rename the file as .txt, then open it using the
text import wizard. In the wizards last screen, you can specify for that
column of dates, how they should be interpreted.

 

[Gareth]

Hi Nigel,

You've already received some answers on this but to give you a specific
example (copied from an answer I posted to someone else a few weeks ago):

<<You can get round this easily by specifying, when opening, how you
want each column to be interpreted. In the below example I've said
columns 1 and 2 should be considered as DMY and column 3 should be MDY.

Sub openfiledates()

Dim myFieldInfo As Variant

myFieldInfo = Array(Array(1, xlDMYFormat), _
Array(2, xlDMYFormat), _
Array(3, xlMDYFormat))

Workbooks.OpenText Filename:="c:\temp\temp.txt", _
DataType:=xlDelimited, _
Comma:=True, _
FieldInfo:=myFieldInfo

End Sub

 

[Nigel]

Of course, thanks Rich_z for pointing this out. I can get the source fixed
so that will resolve the problem.

 

[Nigel]

Hi Tom
Yes, a good option but the target application is running in xl97 so the
level of control is limited. I will be getting the source changed to the
specification that Rich_z reminded me of. That is dates as CCYYMMDD. Thanks
for your reply

 

[Guest]

Hi,

I embedded a hyperlink into a worksheet and used the "hyperlinks.follow"
command in my code eg:

myWorksheet.Cells(RowIndex, lSrc_ci).Hyperlinks(1).Follow

It opens the document as if it has been opened manually (without the date
format problem). Don't know if you can do this without having to embed a
hyperlink in a worksheet...hope this helps

l