Marco for save excel sheet as a CSV with semicolon

[Daniel Chang]

Happy new year to all.

I have reocrded a marco to save a excel sheet to a csv file as below, however, the default separator is comma, but we need semicolon. I have tried changing the List separator from Control Panel but still failed. Is there anyone can help to modify this marco and add the separator as semecolon?

ActiveWorkbook.SaveAs Filename:= _
"c:\MyFile.csv", FileFormat:=xlCSV _
, CreateBackup:=False

[patel45]

Please Login or Register  to view this content.

[Daniel Chang]

Hi, Thanks for quick reply, it works but the file comes out with
TWDOLLAR;
A97101;
TW1;
S;
0.7475;
0.5;
DBSTP AFS GVB;
DTP FO_STY;
20120816

While we need it to be TWDOLLAR;A97101;TW1;S;0.7475;0.5;DBSTP AFS GVB;DTP FO_STY;20120816
Please advise how to adjust the code.

[patel45]

attach a sample of input file with also desired result

[Daniel Chang]

It's solved by using the following marco.

Public Sub CharacterSV()
Const DELIMITER As String = ";"
Dim myRecord As Range
Dim myField As Range
Dim nFileNum As Long
Dim sOut As String

nFileNum = FreeFile
Open "Test.txt" For Output As #nFileNum
For Each myRecord In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
With myRecord
For Each myField In Range(.Cells, _
Cells(.Row, Columns.Count).End(xlToLeft))
sOut = sOut & DELIMITER & myField.Text
Next myField
Print #nFileNum, Mid(sOut, 2)
sOut = Empty
End With
Next myRecord
Close #nFileNum
End Sub