change csv list separator per file?

  • Thread starter Thread starter brt
  • Start date Start date
B

brt

Hi all,

for my work I have to generate different .csv files. Some have a comma
"," as list separator, other files need ";"

Is there a workaround so that I don't have to change the list
separator setting in my regional settings?

And/or can I add a "replace ";" by ","" in a macro that converts
workbout in a range of .csv sheets?


Thanx for your aid,
brt!
 
use the "Text to Columns" option on the data menu.

Choose delimited, check the boxes for ,(comma) and ;(semi colon)

The preview will show how the data has been split, click "finish" and
your data will be in columns.

Hope this helps
Mike
 
Hi Mike,

thnx for your fast reply. I am however especially interested in how to
save a csv file with overwriting the regional list separator ";" into
",".

brt
 
brt,

Here's a macro that may be of help, it allows you to specify the seperator
if you want tab separator type "tab" without quotes.

Sub ExportCSV()
Dim Outfile As String, Sep As String
Dim Rcount As Long, CCount As Integer
Dim fs, f
Outfile = Application.GetSaveAsFilename(FileFilter:="CSV (Comma delimited)(*.csv), *.csv")
If Outfile = "False" Then Exit Sub
Sep = InputBox("Enter your desired delimiter (for tab delimited type tab)")
If Sep = "" Then Exit Sub
If LCase(Sep) = "tab" Then Sep = vbTab
ActiveSheet.UsedRange.Select
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.OpenTextFile(Outfile, 2, -2)
For Rcount = 1 To Selection.Rows.Count
For CCount = 1 To Selection.Columns.Count
Outline = CStr(Selection.Cells(Rcount, CCount).Text)
f.Write Outline
If CCount <> Selection.Columns.Count Then f.Write Sep
Next
f.Write vbCrLf
Next
f.Close
Set f = Nothing
Set fs = Nothing
End Sub

Watch out for line wrap the only lines not tabbed in are Sub and End Sub

Dan E
 
Back
Top