why does it matter what the name of the file is? I already said it was a txt
file. take source as xyz.txt- it isn't relevant.

Dave- if you have the same version of excel as me which i'm am assuming you
do as I specified this is a 2003 upgrade issue, then why not try what I am
actually saying my problem is on your machine. I will be very surprised if
you don't find the same as i have tried this on numerous computers.

Any other help apprecitaed....


"Dave Peterson" wrote:

Not for me.

But if you say all you do is hit F2 and enter and the offending cell becomes a
date, then I have no idea what's going on.

And you still haven't shared the name of the file (what's in Source)????



jz193 wrote:

okay- think i understand your suggestion.

The format of dates in the text files is actually ddmmyy so for the len
command I acutally get 8. Does that shed any light on this immensely
annoying problem!?

"Dave Peterson" wrote:

What's the value of the variable Source?

If the name of that input file is *.csv, then excel will ignore your code for
each field.

If you find one of those offending cells (imported as text), what happens if you
use:

=len(x9)
(change the cell's address)

do you really get 10? dd/mm/yyyy



jz193 wrote:

I can;t see in my code where my specification of the format is actually- do I
need to add somethng to this (this is just a recorded macro) to do this?

Workbooks.OpenText FileName:= source, Origin _
:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=True, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1,
1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1),
Array(7, 1), Array(8, 1), _
Array(9, 4), Array(10, 4), Array(11, 1), Array(12, 1), Array(13, 1),
Array(14, 1), Array(15 _
, 1), Array(16, 1), Array(17, 1), Array(18, 1))

"Dave Peterson" wrote:

Are you sure you choose the correct format for that field? mdy, dmy, ...

I've never seen excel fail to convert something that looked like that kind of
date to a date (during the text import).

jz193 wrote:

hi- thanks for the tip. I have already done this- the only way it works once
the macros is finished is to manually go into the cell and press F2 and
return. Any other suggestions appreciated.

"Pete" wrote:

Do it manually and record a macro while you do it. Set the column
formats for Date DMY as appropriate. Then you will be able to merge
bits of the recorded macro into the macro you have already developed.

Hope this helps.

Pete



--

Dave Peterson


--

Dave Peterson


--

Dave Peterson