Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
jz193
 
Posts: n/a
Default opening text file in excel problem

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
  #2   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default opening text file in excel problem

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
  #3   Report Post  
Posted to microsoft.public.excel.misc
jz193
 
Posts: n/a
Default opening text file in excel problem

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

  #4   Report Post  
Posted to microsoft.public.excel.misc
Pete
 
Posts: n/a
Default opening text file in excel problem

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

  #5   Report Post  
Posted to microsoft.public.excel.misc
jz193
 
Posts: n/a
Default opening text file in excel problem

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




  #6   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default opening text file in excel problem

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
  #7   Report Post  
Posted to microsoft.public.excel.misc
jz193
 
Posts: n/a
Default opening text file in excel problem


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?


"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

  #8   Report Post  
Posted to microsoft.public.excel.misc
jz193
 
Posts: n/a
Default opening text file in excel problem

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

  #9   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default opening text file in excel problem

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
  #10   Report Post  
Posted to microsoft.public.excel.misc
jz193
 
Posts: n/a
Default opening text file in excel problem

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.


"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



  #11   Report Post  
Posted to microsoft.public.excel.misc
jz193
 
Posts: n/a
Default opening text file in excel problem

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

  #12   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default opening text file in excel problem

Find one of those cells that looks like a date--but isn't.

Say it's in X9 (or whatever)
find an empty cell and put:
=len(x9)
to see what it returns.

If it's not 10 (dd/mm/yyyy has a length of 10), then there's other things in
that cell that don't belong--maybe that's what's causing the trouble with the
import.



jz193 wrote:

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.

"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
  #13   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default opening text file in excel problem

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
  #14   Report Post  
Posted to microsoft.public.excel.misc
jz193
 
Posts: n/a
Default opening text file in excel problem

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

  #15   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default opening text file in excel problem

The name of the file is very relevant.

You said the file is a text file--not .txt.

..CSV files are text files. But files with .csv extensions won't behave nicely
in macros.

I put this into a text file named edit2.txt.

050206
050206
050206
050206


I ran this macro:
Option Explicit
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 02/06/2006 by David Peterson
'
'
Workbooks.OpenText Filename:="C:\My Documents\excel\Edit2.txt", _
Origin:=437 _
, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(1, 4), _
TrailingMinusNumbers:=True
Columns("A:A").EntireColumn.AutoFit
End Sub

It worked fine.



jz193 wrote:

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


--

Dave Peterson


  #16   Report Post  
Posted to microsoft.public.excel.misc
jz193
 
Posts: n/a
Default opening text file in excel problem

okay- well apoligies if i didn;t specifiy it being a .txt file- I thought the
fact that i never once metioned csv would make it obvious.

As I;ve said before, I;m more interested in why I have the problem rather
than why you don't. I;ve said numerous times what my problem is and that it
is not imaginary, so would appreciate something constructive. Isn't that the
purpose of a help forum? If you can't provide that help, why post? .

I can see no difference between yours and my code so assume it may be a
setting in excel? does anyone else have any solutions?
"Dave Peterson" wrote:

Find one of those cells that looks like a date--but isn't.

Say it's in X9 (or whatever)
find an empty cell and put:
=len(x9)
to see what it returns.

If it's not 10 (dd/mm/yyyy has a length of 10), then there's other things in
that cell that don't belong--maybe that's what's causing the trouble with the
import.



jz193 wrote:

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.

"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

  #17   Report Post  
Posted to microsoft.public.excel.misc
jz193
 
Posts: n/a
Default opening text file in excel problem

Dave- you haven;t been reading my posts carefully enough.

Wht don't you try changing the 05 to anything 12.

Does it still "work fine" becasue as i have said, I have tried several
computers. Even if it does, that doesn;t help me or anyone else. Lets try
and keep this thread constructive for other people.

"Dave Peterson" wrote:

Find one of those cells that looks like a date--but isn't.

Say it's in X9 (or whatever)
find an empty cell and put:
=len(x9)
to see what it returns.

If it's not 10 (dd/mm/yyyy has a length of 10), then there's other things in
that cell that don't belong--maybe that's what's causing the trouble with the
import.



jz193 wrote:

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.

"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

  #18   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default opening text file in excel problem

Good luck.

jz193 wrote:

Dave- you haven;t been reading my posts carefully enough.

Wht don't you try changing the 05 to anything 12.

Does it still "work fine" becasue as i have said, I have tried several
computers. Even if it does, that doesn;t help me or anyone else. Lets try
and keep this thread constructive for other people.

"Dave Peterson" wrote:

Find one of those cells that looks like a date--but isn't.

Say it's in X9 (or whatever)
find an empty cell and put:
=len(x9)
to see what it returns.

If it's not 10 (dd/mm/yyyy has a length of 10), then there's other things in
that cell that don't belong--maybe that's what's causing the trouble with the
import.



jz193 wrote:

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.

"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


--

Dave Peterson
  #19   Report Post  
Posted to microsoft.public.excel.misc
jz193
 
Posts: n/a
Default opening text file in excel problem

dave- I thought it was obvious that it was a .txt file from the fact I never
mentioned CSV.

Whilst i appreciate your help, you haven't been reading my posts carefully.
Try changing your 05 to 12- see if that works. As I have pointed out
several times, it is only then the problem arises.

If you donlt get the problem- thats great. But I do, so would appreciate
any help- lets keep this thread constructive for other people who might get
the problem.



"Dave Peterson" wrote:

The name of the file is very relevant.

You said the file is a text file--not .txt.

..CSV files are text files. But files with .csv extensions won't behave nicely
in macros.

I put this into a text file named edit2.txt.

050206
050206
050206
050206


I ran this macro:
Option Explicit
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 02/06/2006 by David Peterson
'
'
Workbooks.OpenText Filename:="C:\My Documents\excel\Edit2.txt", _
Origin:=437 _
, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(1, 4), _
TrailingMinusNumbers:=True
Columns("A:A").EntireColumn.AutoFit
End Sub

It worked fine.



jz193 wrote:

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


--

Dave Peterson

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Microsoft Office XP Excel file opening problem Struth Setting up and Configuration of Excel 0 November 9th 05 01:48 AM
Excel getting Error - file format is not valid WHEN OPENING babu Excel Discussion (Misc queries) 1 August 24th 05 10:09 PM
Opening a text file in excel Opyuse Excel Discussion (Misc queries) 4 June 13th 05 10:23 PM
Opening an Excel file Sheldon Zaklow Excel Discussion (Misc queries) 0 March 3rd 05 11:29 PM
Read Text File into Excel Using VBA Willie T Excel Discussion (Misc queries) 13 January 8th 05 01:37 AM


All times are GMT +1. The time now is 06:39 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"