CSV export, deliminating character

[Hannes Prokop]

I am trying to export an excel file to csv. But unlike the name
"comma-separated..." suggests excel uses semicolons to seperate my
cells. I would need the cells separated by commas rather.

As some of my cells contain semicolons as data, the workaround of
replacing all the ; with , in wordpad does not work. I already tried
to change the list-seperate character in windows-regional options but
that didn't work either.

Thanks in advance...
Hannes

 

[Jim Rech]

Changing your regional settings list separator should definitely do it,
Hannes. It does for me. You may have to restart Excel after the switch,
however, to get it to recognize the change.

If all else fails you can use a macro to create the CSV. See below.

--
Jim Rech
Excel MVP

'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 commas 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