software

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

gt; I'd always rename the text file to *.txt (from (*.csv????). Then when I open
gt; it, I can see the import wizard open up and choose how I want each field
gt; treated.
gt;
gt; jz193 wrote:
gt; gt;
gt; gt; Hi- hope someone can help with this pretty specific problem- I have just
gt; gt; upgraded to a new version of excel (2003), and have an issue with when I am
gt; gt; opening text files in excel.
gt; gt; If I open the txt value manually, I have no problem, all cells with dates in
gt; gt; them are recognised as dates.
gt; gt; When I open this file and bring it in using VBA, it recognises some of the
gt; gt; dates as text, and so screws my calculations.
gt; gt; I'm pretty sure that this is because it tries to bring them in in american
gt; gt; format, but has a problem when what it sees as the month goes above 12 (i.e
gt; gt; it brings it in as mm/dd/yyyy whereas the file is dd/mm/yyyy but this only
gt; gt; causes a problem if ddgt;12).
gt; gt; Of course I can correct this using date value or whatever, but I don;t want
gt; gt; to have to- is there a simple solution?
gt; gt;
gt; gt; any help would be massively appreciated.
gt; gt; thanks in advance
gt;
gt; --
gt;
gt; Dave Peterson
gt;

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.

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

quot;Petequot; wrote:

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

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:
gt;
gt; hi- thanks for the tip. I have already done this- the only way it works once
gt; the macros is finished is to manually go into the cell and press F2 and
gt; return. Any other suggestions appreciated.
gt;
gt; quot;Petequot; wrote:
gt;
gt; gt; Do it manually and record a macro while you do it. Set the column
gt; gt; formats for Date DMY as appropriate. Then you will be able to merge
gt; gt; bits of the recorded macro into the macro you have already developed.
gt; gt;
gt; gt; Hope this helps.
gt; gt;
gt; gt; Pete
gt; gt;
gt; gt;

--

Dave Peterson


Maybe I havent been clear- when I do this manually, it has no problem. It
is when I do this using VBA that the problem occurs- using opentext seems to
revert VBA to USA focus (regardless of the formatting I use i.e dmy etc...),
and so any dates it sees as US and defines dates outisde of acceptbal;e US
range as text.

Dave- maybe your a different version of Excel to me- I don't know. Frankly,
I would rather focus on why I have my problem then why you don't!Any other suggestions?quot;Dave Petersonquot; wrote:

gt; Are you sure you choose the correct format for that field? mdy, dmy, ...
gt;
gt; I've never seen excel fail to convert something that looked like that kind of
gt; date to a date (during the text import).
gt;
gt; jz193 wrote:
gt; gt;
gt; gt; hi- thanks for the tip. I have already done this- the only way it works once
gt; gt; the macros is finished is to manually go into the cell and press F2 and
gt; gt; return. Any other suggestions appreciated.
gt; gt;
gt; gt; quot;Petequot; wrote:
gt; gt;
gt; gt; gt; Do it manually and record a macro while you do it. Set the column
gt; gt; gt; formats for Date DMY as appropriate. Then you will be able to merge
gt; gt; gt; bits of the recorded macro into the macro you have already developed.
gt; gt; gt;
gt; gt; gt; Hope this helps.
gt; gt; gt;
gt; gt; gt; Pete
gt; gt; gt;
gt; gt; gt;
gt;
gt; --
gt;
gt; Dave Peterson
gt;

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

quot;Dave Petersonquot; wrote:

gt; Are you sure you choose the correct format for that field? mdy, dmy, ...
gt;
gt; I've never seen excel fail to convert something that looked like that kind of
gt; date to a date (during the text import).
gt;
gt; jz193 wrote:
gt; gt;
gt; gt; hi- thanks for the tip. I have already done this- the only way it works once
gt; gt; the macros is finished is to manually go into the cell and press F2 and
gt; gt; return. Any other suggestions appreciated.
gt; gt;
gt; gt; quot;Petequot; wrote:
gt; gt;
gt; gt; gt; Do it manually and record a macro while you do it. Set the column
gt; gt; gt; formats for Date DMY as appropriate. Then you will be able to merge
gt; gt; gt; bits of the recorded macro into the macro you have already developed.
gt; gt; gt;
gt; gt; gt; Hope this helps.
gt; gt; gt;
gt; gt; gt; Pete
gt; gt; gt;
gt; gt; gt;
gt;
gt; --
gt;
gt; Dave Peterson
gt;

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:
gt;
gt; I can;t see in my code where my specification of the format is actually- do I
gt; need to add somethng to this (this is just a recorded macro) to do this?
gt;
gt; Workbooks.OpenText FileName:= source, Origin _
gt; :=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
gt; xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
gt; Semicolon:=True, _
gt; Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1,
gt; 1), _
gt; Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1),
gt; Array(7, 1), Array(8, 1), _
gt; Array(9, 4), Array(10, 4), Array(11, 1), Array(12, 1), Array(13, 1),
gt; Array(14, 1), Array(15 _
gt; , 1), Array(16, 1), Array(17, 1), Array(18, 1))
gt;
gt; quot;Dave Petersonquot; wrote:
gt;
gt; gt; Are you sure you choose the correct format for that field? mdy, dmy, ...
gt; gt;
gt; gt; I've never seen excel fail to convert something that looked like that kind of
gt; gt; date to a date (during the text import).
gt; gt;
gt; gt; jz193 wrote:
gt; gt; gt;
gt; gt; gt; hi- thanks for the tip. I have already done this- the only way it works once
gt; gt; gt; the macros is finished is to manually go into the cell and press F2 and
gt; gt; gt; return. Any other suggestions appreciated.
gt; gt; gt;
gt; gt; gt; quot;Petequot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Do it manually and record a macro while you do it. Set the column
gt; gt; gt; gt; formats for Date DMY as appropriate. Then you will be able to merge
gt; gt; gt; gt; bits of the recorded macro into the macro you have already developed.
gt; gt; gt; gt;
gt; gt; gt; gt; Hope this helps.
gt; gt; gt; gt;
gt; gt; gt; gt; Pete
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson
gt; gt;

--

Dave Peterson

source is defineitly a txt file not csv-

Dave, appreicate your continued help on this- the len(x9) suggestion you
made I didn't really understand- would you be able to clarify that a bit?
thanks a lot.quot;Dave Petersonquot; wrote:

gt; What's the value of the variable Source?
gt;
gt; If the name of that input file is *.csv, then excel will ignore your code for
gt; each field.
gt;
gt; If you find one of those offending cells (imported as text), what happens if you
gt; use:
gt;
gt; =len(x9)
gt; (change the cell's address)
gt;
gt; do you really get 10? dd/mm/yyyy
gt;
gt;
gt;
gt; jz193 wrote:
gt; gt;
gt; gt; I can;t see in my code where my specification of the format is actually- do I
gt; gt; need to add somethng to this (this is just a recorded macro) to do this?
gt; gt;
gt; gt; Workbooks.OpenText FileName:= source, Origin _
gt; gt; :=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
gt; gt; xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
gt; gt; Semicolon:=True, _
gt; gt; Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1,
gt; gt; 1), _
gt; gt; Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1),
gt; gt; Array(7, 1), Array(8, 1), _
gt; gt; Array(9, 4), Array(10, 4), Array(11, 1), Array(12, 1), Array(13, 1),
gt; gt; Array(14, 1), Array(15 _
gt; gt; , 1), Array(16, 1), Array(17, 1), Array(18, 1))
gt; gt;
gt; gt; quot;Dave Petersonquot; wrote:
gt; gt;
gt; gt; gt; Are you sure you choose the correct format for that field? mdy, dmy, ...
gt; gt; gt;
gt; gt; gt; I've never seen excel fail to convert something that looked like that kind of
gt; gt; gt; date to a date (during the text import).
gt; gt; gt;
gt; gt; gt; jz193 wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; hi- thanks for the tip. I have already done this- the only way it works once
gt; gt; gt; gt; the macros is finished is to manually go into the cell and press F2 and
gt; gt; gt; gt; return. Any other suggestions appreciated.
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Petequot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; Do it manually and record a macro while you do it. Set the column
gt; gt; gt; gt; gt; formats for Date DMY as appropriate. Then you will be able to merge
gt; gt; gt; gt; gt; bits of the recorded macro into the macro you have already developed.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Hope this helps.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Pete
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt;
gt; gt; gt; Dave Peterson
gt; gt; gt;
gt;
gt; --
gt;
gt; Dave Peterson
gt;