RE: Export to CSV file problems

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.


.

Relevant Pages

  • RE: Export records into a text file based on criteria
    ... you plan to keep on opening and closing the CSV file for each updated record? ... For ColCount = 1 To LastCol ... OutputLine = OutputLine & Delimiter & Cells ...
    (microsoft.public.excel.programming)
  • RE: Export records into a text file based on criteria
    ... There is going to be a sharing problem. ... For ColCount = 1 To LastCol ... OutputLine = OutputLine & Delimiter & Cells ...
    (microsoft.public.excel.programming)
  • RE: Export records into a text file based on criteria
    ... For ColCount = 1 To LastCol ... OutputLine = OutputLine & Delimiter & Cells ...
    (microsoft.public.excel.programming)
  • RE: Export records into a text file based on criteria
    ... Not too sure how Excel would work out if the record has been updated or not? ... The csv would have to be opened and closed I think in order to allow other ... For ColCount = 1 To LastCol ... OutputLine = OutputLine & Delimiter & Cells ...
    (microsoft.public.excel.programming)
  • RE: Getting around 255 Column limit
    ... CSV files are used for millions *billions) of different applications. ... If OutputLine = "" Then ... OutputLine = .Cells(RowCount, ColCount) ... How do I get data from multiple sheets into one CSV? ...
    (microsoft.public.excel.programming)