In 2007 it seems like it does not care about the choices in the advanced
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
| 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
| > | > | 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
| > | > | > | > |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
| > | > | > | > | > | > | 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?
| > | > | >
| > | > | >
| > | > | >
| > | >
| > | >
| > | >
| >
| >
| >
