I don't know why in Denmark they would call a file with ~ seperating
characters when CSV means Comma Seprated Values. does ~ mean Comma in Demark?

Any way, below is code that you can use to manually generatte the CSV file
with ~


Sub WriteCSV()

Const Delimiter = "~"

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const MyPath = "C:\temp\"
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0


Set fswrite = CreateObject("Scripting.FileSystemObject")

WriteFileName = "text.csv"


'open files
WritePathName = MyPath + WriteFileName
fswrite.CreateTextFile WritePathName
Set fwrite = fswrite.GetFile(WritePathName)
Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault)

lastrow = Cells(Rows.Count, "A").End(xlUp).Row

For RowCount = 1 To lastrow
LastCol = Cells(RowCount, Columns.Count).End(xlToLeft).Column
For ColCount = 1 To LastCol
If ColCount = 1 Then
OutputLine = Cells(RowCount, ColCount)
Else
OutputLine = OutputLine & Delimiter & Cells(RowCount, ColCount)
End If
Next ColCount
tswrite.writeline OutputLine
Next RowCount

tswrite.Close

Exit Sub
End Sub


"Ixtreme" wrote:

Hi all,

I have MS Excel 2002 SP3 installed.
I need to create a CSV file with ~ as the delimiter. I customized my
current (dutch) Regional settings List delimiter to ~. So far so good.
Once I open my excel file and do a save as, select CSV file and click
on Save, I have exactly what I need. Upon opening the file with
Notepad, all records are separeted with ~.

However, I would like to automate this. I record a macro of the above
action which resulted in something like this:

ActiveWorkbook.SaveAs Filename:= _
"C:\FILES\TEST.csv", _
FileFormat:=xlCSV, CreateBackup:=False

But If I open that file with Notepad, all fields are separeted by
comma's :-(

The questions is: WHY? and how can I prevent this from happening? I
want my ~ as the delimiter.