semicolon separated CSV file

[Guest]

How can I save a semicolon separated CSV file from an Excel worksheet?
Selecting the CSV file type in SaveAs function results in a comma separated
file, despite the Windows list separator is semicolon. Using the CSV (MS DOS)
file type creates a semicolon separated file, but it is not suitable for me,
because it changes my 1250 code page to 437.

 

[Jim Rech]

If the built-in Save As formats do not suit your needs your only recourse is
to use a macro. This may do what you want:

''No quotes around strings
''Outputs the selection if more than one cell is selected, else entire sheet
Sub OutputActiveSheetAsTrueCSVFile()
Dim SrcRg As Range
Dim CurrRow As Range
Dim CurrCell As Range
Dim CurrTextStr As String
Dim ListSep As String
Dim FName As Variant
FName = Application.GetSaveAsFilename("", "CSV File (*.csv), *.csv")
If FName <> False Then
ListSep = Application.International(xlListSeparator)
'ListSep = ";" 'use this to force semicolons as separator regardless
of regional settings
If Selection.Cells.Count > 1 Then
Set SrcRg = Selection
Else
Set SrcRg = ActiveSheet.UsedRange
End If
Open FName For Output As #1
For Each CurrRow In SrcRg.Rows
CurrTextStr = ""
For Each CurrCell In CurrRow.Cells
CurrTextStr = CurrTextStr & CurrCell.Value & ListSep
Next
While Right(CurrTextStr, 1) = ListSep
CurrTextStr = Left(CurrTextStr, Len(CurrTextStr) - 1)
Wend
Print #1, CurrTextStr
Next
Close #1
End If
End Sub

--
Jim Rech
Excel MVP
| How can I save a semicolon separated CSV file from an Excel worksheet?
| Selecting the CSV file type in SaveAs function results in a comma
separated
| file, despite the Windows list separator is semicolon. Using the CSV (MS
DOS)
| file type creates a semicolon separated file, but it is not suitable for
me,
| because it changes my 1250 code page to 437.
|

 

[scottymelloty]

ive had the opposite problem , i want to save a csv file with commas,
but they keep saving as semi colons even tho the seprator is set to a
comma ? any ideas ??

 

[Guest]

Thanks, Jim, it works fine, even without forcing the use of ";". I think,
this is an Excel bug, because the SaveAs function should use the
"Application.International(xlListSeparator)" value (it does not).

Regards



„Jim Rech†ezt írta:

 

[Guest]

I suppose it depends on the language version. I use a Hungarian version, and
it seems to use commas regardless the list separator setting. I suppose you
use English version using semicolons also regardless the list separator
setting. My tip: use Jim's sub forcing it to use ","

Stefi


„scottymelloty†ezt írta: