Problem opening a komma separated file with Excel 97

  • Thread starter Thread starter Kristiaan Soomers
  • Start date Start date
K

Kristiaan Soomers

hi all,

I'm having some problems with the above, let's describe
the issue:
I'm working on a windows NT 4.0 machine and I'm using
Excel 97-SR2.
I'm using VBA to open a small komma separated file with
the following code:
workbooks.open Filename:="C:\test.txt",
Origin:=xlWindows, Format:=2
This code puts all the komma separeted fields into
separate collums. This worked perfectly until a few days
ago, now the same code puts the entire content of the file
into a single collumn. There is obviously a problem with
the Format option (2 stands for a komma) which Excel
doesn't interpret correctly anymore.
I tried a slightly different code:
workbooks.open Filename:="C:\test.txt", Origin:=xlWindows,
Format:=6, delimiter:="," (the format:=6 option is used to
override the format and use a custom delimiter) and this
works perfectly again.

I think there's someting wrong with the formatting options
but i'm unable to track down the location of this setting
in Excel (I don't even know if it's possible to change any
of these settings, I doubt it to be quite frankly).

Does anyone have a suggestion?? any help is welcome!

Post it or mail it to (e-mail address removed)

greetz,

Kris
 
Kris,

I think format:=2 actually means delimited, but without
specifying the delimiter, so Excel uses whatever was used
last (it has this bad habit of remembering); apparently
your code stopped "working" when you used some other
delimiter.
Your second approach is the bulletproof one, in that it
specifies the delimiter regardless of what you used last,
so stick with it.

HTH,
Nikos
 
Nikos,

Thnx for you reply and it sounds plausible..however the
VBA help file suggests otherwise (the original file was in
dutch so I ran it through a translator..):

Format variant, optional. If a text file is opened, this
argument indicates the separation sign, such as it is
reflected in the next table. If you omit this argument,
the current separation sign is used.

Value separation sign
1 tab
2 comma
3 space
4 point comma
5 none
6 adapted sign (to see the argument Delimiter)

So format:=2 should work all the time.... do you have some
other ideas?

Kris
 
Kris

So I was on the right track, but made assumptions on the
number specifics... which proved to be the other way
around.

Sorry, no more ideas. The wonders of modern technology!

Regards,
Nikos
 
Back
Top