problem with open text file in excel 2007
 

Hi
I have a textfile from a databasa that I every week opens in excel. I
haven't had any problems with this until we changed to office 2007.
In the textfile there is numbers like 1.072,00- and when I open it in Excel
2003 I get it like -1 072,00 which is exactley what I want. When I do the
same settings in 2007 it seems to ignore them and also sets it as text format
so it looks like 1.072,00-
I have tested it in swedish and english version and I get the same result.

My settings:
Delimiter : tab
Format: general
decimalseparotor (under advanced) : ,
1000-delimiter: .
and I check the little checkbox

Thees settings works perfectley for me in 2003 but not in 2007, is it me or
is it a bug?

problem with open text file in excel 2007
 

When I open a text file in your format (having a TXT extension, not CSV) in
Excel 2007 the Text Import Wizard appears. On the third of three screens
the Advanced button provides several options including treating trailing
minus signs as a negative number. If that's selected it works okay for me.

--
Jim
"Lina" wrote in message
...
| Hi
| I have a textfile from a databasa that I every week opens in excel. I
| haven't had any problems with this until we changed to office 2007.
| In the textfile there is numbers like 1.072,00- and when I open it in
Excel
| 2003 I get it like -1 072,00 which is exactley what I want. When I do the
| same settings in 2007 it seems to ignore them and also sets it as text
format
| so it looks like 1.072,00-
| I have tested it in swedish and english version and I get the same result.
|
| My settings:
| Delimiter : tab
| Format: general
| decimalseparotor (under advanced) : ,
| 1000-delimiter: .
| and I check the little checkbox
|
| Thees settings works perfectley for me in 2003 but not in 2007, is it me
or
| is it a bug?

problem with open text file in excel 2007
 

I have that one selected and it does not work for me in 2007 but it does in
excel 2003.

On the screen with advanced settings in the guide my settings is:
decimalseparotor: ,
1000-delimiter: .
treating trailing minus signs as a negative number: is checked


"Jim Rech" wrote:

When I open a text file in your format (having a TXT extension, not CSV) in
Excel 2007 the Text Import Wizard appears. On the third of three screens
the Advanced button provides several options including treating trailing
minus signs as a negative number. If that's selected it works okay for me.

--
Jim
"Lina" wrote in message
...
| Hi
| I have a textfile from a databasa that I every week opens in excel. I
| haven't had any problems with this until we changed to office 2007.
| In the textfile there is numbers like 1.072,00- and when I open it in
Excel
| 2003 I get it like -1 072,00 which is exactley what I want. When I do the
| same settings in 2007 it seems to ignore them and also sets it as text
format
| so it looks like 1.072,00-
| I have tested it in swedish and english version and I get the same result.
|
| My settings:
| Delimiter : tab
| Format: general
| decimalseparotor (under advanced) : ,
| 1000-delimiter: .
| and I check the little checkbox
|
| Thees settings works perfectley for me in 2003 but not in 2007, is it me
or
| is it a bug?

problem with open text file in excel 2007
 

I switched my Windows Regional settings to a country that uses the . as the
thousands separator and the , as the decimal point. Then I created a text
file using numbers like your example ( 1.072,00- ). It came into Excel
okay. Sorry I don't know what is wrong.

--
Jim
"Lina" wrote in message
...
|I have that one selected and it does not work for me in 2007 but it does in
| excel 2003.
|
| On the screen with advanced settings in the guide my settings is:
| decimalseparotor: ,
| 1000-delimiter: .
| treating trailing minus signs as a negative number: is checked
|
|
| "Jim Rech" wrote:
|
| When I open a text file in your format (having a TXT extension, not CSV)
in
| Excel 2007 the Text Import Wizard appears. On the third of three
screens
| the Advanced button provides several options including treating trailing
| minus signs as a negative number. If that's selected it works okay for
me.
|
| --
| Jim
| "Lina" wrote in message
| ...
| | Hi
| | I have a textfile from a databasa that I every week opens in excel. I
| | haven't had any problems with this until we changed to office 2007.
| | In the textfile there is numbers like 1.072,00- and when I open it in
| Excel
| | 2003 I get it like -1 072,00 which is exactley what I want. When I do
the
| | same settings in 2007 it seems to ignore them and also sets it as text
| format
| | so it looks like 1.072,00-
| | I have tested it in swedish and english version and I get the same
result.
| |
| | My settings:
| | Delimiter : tab
| | Format: general
| | decimalseparotor (under advanced) : ,
| | 1000-delimiter: .
| | and I check the little checkbox
| |
| | Thees settings works perfectley for me in 2003 but not in 2007, is it
me
| or
| | is it a bug?
|
|
|

problem with open text file in excel 2007
 

Thanks for taking time answering this. I just wonder how the number looked in
excel for you, did you get it like - 1 072,00 ?


"Jim Rech" wrote:

I switched my Windows Regional settings to a country that uses the . as the
thousands separator and the , as the decimal point. Then I created a text
file using numbers like your example ( 1.072,00- ). It came into Excel
okay. Sorry I don't know what is wrong.

--
Jim
"Lina" wrote in message
...
|I have that one selected and it does not work for me in 2007 but it does in
| excel 2003.
|
| On the screen with advanced settings in the guide my settings is:
| decimalseparotor: ,
| 1000-delimiter: .
| treating trailing minus signs as a negative number: is checked
|
|
| "Jim Rech" wrote:
|
| When I open a text file in your format (having a TXT extension, not CSV)
in
| Excel 2007 the Text Import Wizard appears. On the third of three
screens
| the Advanced button provides several options including treating trailing
| minus signs as a negative number. If that's selected it works okay for
me.
|
| --
| Jim
| "Lina" wrote in message
| ...
| | Hi
| | I have a textfile from a databasa that I every week opens in excel. I
| | haven't had any problems with this until we changed to office 2007.
| | In the textfile there is numbers like 1.072,00- and when I open it in
| Excel
| | 2003 I get it like -1 072,00 which is exactley what I want. When I do
the
| | same settings in 2007 it seems to ignore them and also sets it as text
| format
| | so it looks like 1.072,00-
| | I have tested it in swedish and english version and I get the same
result.
| |
| | My settings:
| | Delimiter : tab
| | Format: general
| | decimalseparotor (under advanced) : ,
| | 1000-delimiter: .
| | and I check the little checkbox
| |
| | Thees settings works perfectley for me in 2003 but not in 2007, is it
me
| or
| | is it a bug?
|
|
|

problem with open text file in excel 2007
 

I picked German settings so the numbers appeared like this: -1.072,00

Do numbers without trailing minus signs come in okay or does Excel treat
them as text too.? You can tell by looking at their alignment. If the
numbers are left aligned in the cell they are text. In that case I think
the problem is not the minus signs but the thousands separator, etc. Excel
2007 would seem to be acting as it would if you had US settings.


--
Jim
"Lina" wrote in message
...
| Thanks for taking time answering this. I just wonder how the number looked
in
| excel for you, did you get it like - 1 072,00 ?
|
|
| "Jim Rech" wrote:
|
| I switched my Windows Regional settings to a country that uses the . as
the
| thousands separator and the , as the decimal point. Then I created a
text
| file using numbers like your example ( 1.072,00- ). It came into Excel
| okay. Sorry I don't know what is wrong.
|
| --
| Jim
| "Lina" wrote in message
| ...
| |I have that one selected and it does not work for me in 2007 but it
does in
| | excel 2003.
| |
| | On the screen with advanced settings in the guide my settings is:
| | decimalseparotor: ,
| | 1000-delimiter: .
| | treating trailing minus signs as a negative number: is checked
| |
| |
| | "Jim Rech" wrote:
| |
| | When I open a text file in your format (having a TXT extension, not
CSV)
| in
| | Excel 2007 the Text Import Wizard appears. On the third of three
| screens
| | the Advanced button provides several options including treating
trailing
| | minus signs as a negative number. If that's selected it works okay
for
| me.
| |
| | --
| | Jim
| | "Lina" wrote in message
| | ...
| | | Hi
| | | I have a textfile from a databasa that I every week opens in
excel. I
| | | haven't had any problems with this until we changed to office
2007.
| | | In the textfile there is numbers like 1.072,00- and when I open
it in
| | Excel
| | | 2003 I get it like -1 072,00 which is exactley what I want. When I
do
| the
| | | same settings in 2007 it seems to ignore them and also sets it as
text
| | format
| | | so it looks like 1.072,00-
| | | I have tested it in swedish and english version and I get the same
| result.
| | |
| | | My settings:
| | | Delimiter : tab
| | | Format: general
| | | decimalseparotor (under advanced) : ,
| | | 1000-delimiter: .
| | | and I check the little checkbox
| | |
| | | Thees settings works perfectley for me in 2003 but not in 2007, is
it
| me
| | or
| | | is it a bug?
| |
| |
| |
|
|
|

problem with open text file in excel 2007
 

When I tryed to change the regional settings for excel to the same as in the
file it worked ok. So thats god!

But I still wondering why in 2003 I did not have to do that? I just used the
advanced option in the guide to set the "right" separators for the textfile.

In 2007 it seems like it does not care about the choices in the advanced
options in the guide, and just goes for the system settings. For me it´s not
a problem to change :) but some other people here are not so happy having to
change the excel settings between different jobs. And I´m the one having to
tell them that 2007 is better ;)

Any way, many thanks to you!
/Lina

"Jim Rech" wrote:

I picked German settings so the numbers appeared like this: -1.072,00

Do numbers without trailing minus signs come in okay or does Excel treat
them as text too.? You can tell by looking at their alignment. If the
numbers are left aligned in the cell they are text. In that case I think
the problem is not the minus signs but the thousands separator, etc. Excel
2007 would seem to be acting as it would if you had US settings.


--
Jim
"Lina" wrote in message
...
| Thanks for taking time answering this. I just wonder how the number looked
in
| excel for you, did you get it like - 1 072,00 ?
|
|
| "Jim Rech" wrote:
|
| I switched my Windows Regional settings to a country that uses the . as
the
| thousands separator and the , as the decimal point. Then I created a
text
| file using numbers like your example ( 1.072,00- ). It came into Excel
| okay. Sorry I don't know what is wrong.
|
| --
| Jim
| "Lina" wrote in message
| ...
| |I have that one selected and it does not work for me in 2007 but it
does in
| | excel 2003.
| |
| | On the screen with advanced settings in the guide my settings is:
| | decimalseparotor: ,
| | 1000-delimiter: .
| | treating trailing minus signs as a negative number: is checked
| |
| |
| | "Jim Rech" wrote:
| |
| | When I open a text file in your format (having a TXT extension, not
CSV)
| in
| | Excel 2007 the Text Import Wizard appears. On the third of three
| screens
| | the Advanced button provides several options including treating
trailing
| | minus signs as a negative number. If that's selected it works okay
for
| me.
| |
| | --
| | Jim
| | "Lina" wrote in message
| | ...
| | | Hi
| | | I have a textfile from a databasa that I every week opens in
excel. I
| | | haven't had any problems with this until we changed to office
2007.
| | | In the textfile there is numbers like 1.072,00- and when I open
it in
| | Excel
| | | 2003 I get it like -1 072,00 which is exactley what I want. When I
do
| the
| | | same settings in 2007 it seems to ignore them and also sets it as
text
| | format
| | | so it looks like 1.072,00-
| | | I have tested it in swedish and english version and I get the same
| result.
| | |
| | | My settings:
| | | Delimiter : tab
| | | Format: general
| | | decimalseparotor (under advanced) : ,
| | | 1000-delimiter: .
| | | and I check the little checkbox
| | |
| | | Thees settings works perfectley for me in 2003 but not in 2007, is
it
| me
| | or
| | | is it a bug?
| |
| |
| |
|
|
|

problem with open text file in excel 2007
 

In 2007 it seems like it does not care about the choices in the advanced
options

That's exactly it. If you open the Advanced options after closing it you
see they are all set back. Bad bug.

Your co-workers might try this macro:

Sub OpenTextFile()
Dim FName As Variant
FName = Application.GetOpenFilename("Text files(*.txt),*.txt")
If FName < False Then
Workbooks.OpenText Filename:=FName, StartRow:=1, _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
ConsecutiveDelimiter _
:=False, Semicolon:=True, DecimalSeparator:=",",
ThousandsSeparator:=".", _
TrailingMinusNumbers:=True
End If
End Sub


--
Jim
"Lina" wrote in message
...
| When I tryed to change the regional settings for excel to the same as in
the
| file it worked ok. So thats god!
|
| But I still wondering why in 2003 I did not have to do that? I just used
the
| advanced option in the guide to set the "right" separators for the
textfile.
|
| In 2007 it seems like it does not care about the choices in the advanced
| options in the guide, and just goes for the system settings. For me it´s
not
| a problem to change :) but some other people here are not so happy having
to
| change the excel settings between different jobs. And I´m the one having
to
| tell them that 2007 is better ;)
|
| Any way, many thanks to you!
| /Lina
|
| "Jim Rech" wrote:
|
| I picked German settings so the numbers appeared like this: -1.072,00
|
| Do numbers without trailing minus signs come in okay or does Excel treat
| them as text too.? You can tell by looking at their alignment. If the
| numbers are left aligned in the cell they are text. In that case I
think
| the problem is not the minus signs but the thousands separator, etc.
Excel
| 2007 would seem to be acting as it would if you had US settings.
|
|
| --
| Jim
| "Lina" wrote in message
| ...
| | Thanks for taking time answering this. I just wonder how the number
looked
| in
| | excel for you, did you get it like - 1 072,00 ?
| |
| |
| | "Jim Rech" wrote:
| |
| | I switched my Windows Regional settings to a country that uses the .
as
| the
| | thousands separator and the , as the decimal point. Then I created
a
| text
| | file using numbers like your example ( 1.072,00- ). It came into
Excel
| | okay. Sorry I don't know what is wrong.
| |
| | --
| | Jim
| | "Lina" wrote in message
| | ...
| | |I have that one selected and it does not work for me in 2007 but it
| does in
| | | excel 2003.
| | |
| | | On the screen with advanced settings in the guide my settings is:
| | | decimalseparotor: ,
| | | 1000-delimiter: .
| | | treating trailing minus signs as a negative number: is checked
| | |
| | |
| | | "Jim Rech" wrote:
| | |
| | | When I open a text file in your format (having a TXT extension,
not
| CSV)
| | in
| | | Excel 2007 the Text Import Wizard appears. On the third of
three
| | screens
| | | the Advanced button provides several options including treating
| trailing
| | | minus signs as a negative number. If that's selected it works
okay
| for
| | me.
| | |
| | | --
| | | Jim
| | | "Lina" wrote in message
| | | ...
| | | | Hi
| | | | I have a textfile from a databasa that I every week opens in
| excel. I
| | | | haven't had any problems with this until we changed to office
| 2007.
| | | | In the textfile there is numbers like 1.072,00- and when I
open
| it in
| | | Excel
| | | | 2003 I get it like -1 072,00 which is exactley what I want.
When I
| do
| | the
| | | | same settings in 2007 it seems to ignore them and also sets it
as
| text
| | | format
| | | | so it looks like 1.072,00-
| | | | I have tested it in swedish and english version and I get the
same
| | result.
| | | |
| | | | My settings:
| | | | Delimiter : tab
| | | | Format: general
| | | | decimalseparotor (under advanced) : ,
| | | | 1000-delimiter: .
| | | | and I check the little checkbox
| | | |
| | | | Thees settings works perfectley for me in 2003 but not in
2007, is
| it
| | me
| | | or
| | | | is it a bug?
| | |
| | |
| | |
| |
| |
| |
|
|
|

problem with open text file in excel 2007
 

Thank you! The macro does a god job. I placed it in the personal macro book
for everyone that have the problem.
:)

"Jim Rech" wrote:

In 2007 it seems like it does not care about the choices in the advanced
options

That's exactly it. If you open the Advanced options after closing it you
see they are all set back. Bad bug.

Your co-workers might try this macro:

Sub OpenTextFile()
Dim FName As Variant
FName = Application.GetOpenFilename("Text files(*.txt),*.txt")
If FName < False Then
Workbooks.OpenText Filename:=FName, StartRow:=1, _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
ConsecutiveDelimiter _
:=False, Semicolon:=True, DecimalSeparator:=",",
ThousandsSeparator:=".", _
TrailingMinusNumbers:=True
End If
End Sub


--
Jim
"Lina" wrote in message
...
| When I tryed to change the regional settings for excel to the same as in
the
| file it worked ok. So thats god!
|
| But I still wondering why in 2003 I did not have to do that? I just used
the
| advanced option in the guide to set the "right" separators for the
textfile.
|
| In 2007 it seems like it does not care about the choices in the advanced
| options in the guide, and just goes for the system settings. For me it´s
not
| a problem to change :) but some other people here are not so happy having
to
| change the excel settings between different jobs. And I´m the one having
to
| tell them that 2007 is better ;)
|
| Any way, many thanks to you!
| /Lina
|
| "Jim Rech" wrote:
|
| I picked German settings so the numbers appeared like this: -1.072,00
|
| Do numbers without trailing minus signs come in okay or does Excel treat
| them as text too.? You can tell by looking at their alignment. If the
| numbers are left aligned in the cell they are text. In that case I
think
| the problem is not the minus signs but the thousands separator, etc.
Excel
| 2007 would seem to be acting as it would if you had US settings.
|
|
| --
| Jim
| "Lina" wrote in message
| ...
| | Thanks for taking time answering this. I just wonder how the number
looked
| in
| | excel for you, did you get it like - 1 072,00 ?
| |
| |
| | "Jim Rech" wrote:
| |
| | I switched my Windows Regional settings to a country that uses the .
as
| the
| | thousands separator and the , as the decimal point. Then I created
a
| text
| | file using numbers like your example ( 1.072,00- ). It came into
Excel
| | okay. Sorry I don't know what is wrong.
| |
| | --
| | Jim
| | "Lina" wrote in message
| | ...
| | |I have that one selected and it does not work for me in 2007 but it
| does in
| | | excel 2003.
| | |
| | | On the screen with advanced settings in the guide my settings is:
| | | decimalseparotor: ,
| | | 1000-delimiter: .
| | | treating trailing minus signs as a negative number: is checked
| | |
| | |
| | | "Jim Rech" wrote:
| | |
| | | When I open a text file in your format (having a TXT extension,
not
| CSV)
| | in
| | | Excel 2007 the Text Import Wizard appears. On the third of
three
| | screens
| | | the Advanced button provides several options including treating
| trailing
| | | minus signs as a negative number. If that's selected it works
okay
| for
| | me.
| | |
| | | --
| | | Jim
| | | "Lina" wrote in message
| | | ...
| | | | Hi
| | | | I have a textfile from a databasa that I every week opens in
| excel. I
| | | | haven't had any problems with this until we changed to office
| 2007.
| | | | In the textfile there is numbers like 1.072,00- and when I
open
| it in
| | | Excel
| | | | 2003 I get it like -1 072,00 which is exactley what I want.
When I
| do
| | the
| | | | same settings in 2007 it seems to ignore them and also sets it
as
| text
| | | format
| | | | so it looks like 1.072,00-
| | | | I have tested it in swedish and english version and I get the
same
| | result.
| | | |
| | | | My settings:
| | | | Delimiter : tab
| | | | Format: general
| | | | decimalseparotor (under advanced) : ,
| | | | 1000-delimiter: .
| | | | and I check the little checkbox
| | | |
| | | | Thees settings works perfectley for me in 2003 but not in
2007, is
| it
| | me
| | | or
| | | | is it a bug?
| | |
| | |
| | |
| |
| |
| |
|
|
|