WorkBook.Open: Problems with field separation character

[Ebbe]

I am using these lines to open a semicolon separated file:

Dim ImportFileName As String
ImportFileName = Application.GetOpenFilename("CSV-files (*.csv), *.csv")
Workbooks.Open Filename:=ImportFileName, Format:=6, delimiter:=";"

The file is opened, but the Excel uses "," (comma) as separator.
WHY!

I am running Office 2000 Pro

Ebbe

 

[Nick Hodge]

Ebbe

csv files are seen as a native format for Excel and so, I suspect, it is
seeing the extension and presuming commas.

You can try two routes to fix this...

1) Try using the OpenText method rather than the Open method
2) Try using a file with no extension or a *.txt extension and option 1

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Tom Ogilvy]

In at least xl97 and xl2000, only option 2 will work. The settings in
OpenText are ignored if the file is named with a .csv extension (in my
experience - different regional versions may have different behavior).

 

[Ebbe]

Thank You Nick.
Renaming the .csv file to .txt did the work. The fields game in as expected.

I tried also Toms proposal, but Excel think it know better when the file
extension is .csv :-(
It is clearly very difficult to persuade Excel to treat .csv files in
alternative ways.

Ebbe

 

[Nick Hodge]

Tom

I suspected that but couldn't test. Excel always opens a csv as exactly that
(comma separated values) but I wasn't sure if their are regional variances
as it now starts to appear in some versions regional settings

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Tom Ogilvy]

You must have misread my post (or perhaps I was not clear). I echoed Nick
that you must change to .txt - that .csv does not work.

 

[Nick Hodge]

No, I knew what you were saying, it was with reference to option 1 which I
wasn't sure about ;-)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Tom Ogilvy]

Option 1 said use OpenText (with the file still named with a .csv extension)
Option 2 said use OpenText, but rename the file with a .Txt extension (or no
extension)

I said Using OpentText with a file with a .CSV extention does NOT work as
the settings for OpenText are ignored.


the OP responded that he had renamed the file with a .txt extension and that
worked - which is what I echoed - cautioning that not doing that would not
work. The OP then said he tried my method and it did not work. What method
the OP tried that did not work is a mystery, but it was nothing I suggested.
I was responding to the OP's comment.