How do I set the delimiter to save as a CSV?

[Jeroen Hofs]

How do I set the delimiter when I save a file in CSV format, if my system
settings have the delimiter set to semicolon the saved CSV will automatically
alse be semicolon delimited. I want to be able to define the delimiter when
using the SaveAs method.

 

[Dave Peterson]

Maybe you could use a macro that writes your data:

Earl Kiosterud's Text Write program:
www.smokeylake.com/excel
(or directly: http://www.smokeylake.com/excel/text_write_program.htm)

Chip Pearson's:
http://www.cpearson.com/excel/imptext.htm

J.E. McGimpsey's:
http://www.mcgimpsey.com/excel/textfiles.html

======
Earl's may be sufficient right out of the box. He supports lots of options.

 

[JE McGimpsey]

AFAIK, XL will always use the system settings.

See

http://www.mcgimpsey.com/excel/textfiles.html

for a workaround.

 

[Jeroen Hofs]

Thanks Dave!
I'll check it out right away.
Jeroen

 

[BrianB]

Code:
--------------------

'===============================================
'- MACRO TO CONVERT WORKSHEET TO
'- PIPE (or other) DELIMITED TEXT FILE
'- WS HAS TO BE SET UP AS A SIMPLE TABLE
'- RUN MACRO FROM THE SHEET
'- Should run on any sheet
'- Brian Baulsom April 2002
'===============================================
'-
Sub PipeDelimited()
Dim FileName As String
Dim MyRow As Long
Dim MyCol As Integer
Dim MyReturn As String
Dim ws As Worksheet
Dim LastRow As Long
Dim ColumnCount As Integer
Dim MyDelimiter As String
'--------------------------------------------
'- set the delimiter
MyDelimiter = "|"
'--------------------------------------------
Set ws = ActiveSheet
MyReturn = Chr(13)
FileName = ws.Name & ".txt"
Open FileName For Append As #1
'------------------------------
'- get number of rows
LastRow = ws.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
'--------------------------------------------
'- get number of columns
ColumnCount = ws.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column
'--------------------------------------------
'- loop through rows & columns
For MyRow = 1 To LastRow
For MyCol = 1 To ColumnCount
Print #1, ws.Cells(MyRow, MyCol).Value;
If MyCol < ColumnCount Then Print #1, MyDelimiter;
Next
Print #1, MyReturn; ' end of line Return
Next
'----------------------------------------------
Close #1
MsgBox ("Done")
End Sub

'--------------------------------------------------