WorkBook.Open: Problems with field separation character
 

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

WorkBook.Open: Problems with field separation character
 

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
HIS


"Ebbe" wrote in message
...
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

WorkBook.Open: Problems with field separation character
 

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).

--
Regards,
Tom Ogilvy

"Nick Hodge" wrote in message
...
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
HIS


"Ebbe" wrote in message
...
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

WorkBook.Open: Problems with field separation character
 

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" skrev i en meddelelse
...
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
HIS


"Ebbe" wrote in message
...
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

WorkBook.Open: Problems with field separation character
 

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
HIS


"Tom Ogilvy" wrote in message
...
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).

--
Regards,
Tom Ogilvy

"Nick Hodge" wrote in message
...
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
HIS


"Ebbe" wrote in message
...
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

WorkBook.Open: Problems with field separation character
 

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.

--
Regards,
Tom Ogilvy

"Ebbe" wrote in message
...
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" skrev i en
meddelelse
...
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
HIS


"Ebbe" wrote in message
...
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

WorkBook.Open: Problems with field separation character
 

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
HIS


"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"Ebbe" wrote in message
...
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" skrev i en
meddelelse
...
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
HIS


"Ebbe" wrote in message
...
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

WorkBook.Open: Problems with field separation character
 

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.

--
regards,
Tom Ogilvy


"Nick Hodge" wrote in message
...
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
HIS


"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"Ebbe" wrote in message
...
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" skrev i en
meddelelse
...
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
HIS


"Ebbe" wrote in message
...
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