Hi- hope someone can help with this pretty specific problem- I have just
upgraded to a new version of excel (2003), and have an issue with when I am
opening text files in excel.
If I open the txt value manually, I have no problem, all cells with dates in
them are recognised as dates.
When I open this file and bring it in using VBA, it recognises some of the
dates as text, and so screws my calculations.
I'm pretty sure that this is because it tries to bring them in in american
format, but has a problem when what it sees as the month goes above 12 (i.e
it brings it in as mm/dd/yyyy whereas the file is dd/mm/yyyy but this only
causes a problem if dd12).
Of course I can correct this using date value or whatever, but I don;t want
to have to- is there a simple solution?

any help would be massively appreciated.
thanks in advance

I'd always rename the text file to *.txt (from (*.csv????). Then when I open
it, I can see the import wizard open up and choose how I want each field
treated.

jz193 wrote:

Hi- hope someone can help with this pretty specific problem- I have just
upgraded to a new version of excel (2003), and have an issue with when I am
opening text files in excel.
If I open the txt value manually, I have no problem, all cells with dates in
them are recognised as dates.
When I open this file and bring it in using VBA, it recognises some of the
dates as text, and so screws my calculations.
I'm pretty sure that this is because it tries to bring them in in american
format, but has a problem when what it sees as the month goes above 12 (i.e
it brings it in as mm/dd/yyyy whereas the file is dd/mm/yyyy but this only
causes a problem if dd12).
Of course I can correct this using date value or whatever, but I don;t want
to have to- is there a simple solution?

any help would be massively appreciated.
thanks in advance

--

Dave Peterson

thanks for the response- actually- it is already a text file so I can;t use
this.

Any other help would be much appeciated.



"Dave Peterson" wrote:

I'd always rename the text file to *.txt (from (*.csv????). Then when I open
it, I can see the import wizard open up and choose how I want each field
treated.

jz193 wrote:

Hi- hope someone can help with this pretty specific problem- I have just
upgraded to a new version of excel (2003), and have an issue with when I am
opening text files in excel.
If I open the txt value manually, I have no problem, all cells with dates in
them are recognised as dates.
When I open this file and bring it in using VBA, it recognises some of the
dates as text, and so screws my calculations.
I'm pretty sure that this is because it tries to bring them in in american
format, but has a problem when what it sees as the month goes above 12 (i.e
it brings it in as mm/dd/yyyy whereas the file is dd/mm/yyyy but this only
causes a problem if dd12).
Of course I can correct this using date value or whatever, but I don;t want
to have to- is there a simple solution?

any help would be massively appreciated.
thanks in advance

--

Dave Peterson

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

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

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