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.

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.

Jeroen Hofs wrote:

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

AFAIK, XL will always use the system settings.

See

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

for a workaround.


In article ,
"Jeroen Hofs" <Jeroen wrote:

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.

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

"Dave Peterson" wrote:

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.

Jeroen Hofs wrote:

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

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

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

--------------------


--
BrianB


------------------------------------------------------------------------
BrianB's Profile: http://www.excelforum.com/member.php...info&userid=55
View this thread: http://www.excelforum.com/showthread...hreadid=388031