use vba creat csv but the format of csv is not correct.

Question

I use the code as below.

   Private Sub CommandButton1_Click()
    Const File$ = "C:\CsvfileTest2.csv"
    Dim Fso, MyFile

    Set Fso = CreateObject("Scripting.FileSystemObject")
    Set MyFile = Fso.CreateTextFile(File, True)

    MyFile.Close
    With CreateObject("ADODB.Stream")
        .Open
        .LoadFromFile File
        .Position = 0
        .Charset = "UTF-8"
        .SaveToFile File, 2
        .Close
    End With

    'Const ForReading = 1, ForWriting = 2, ForAppending = 8
    Set MyFile = Fso.OpenTextFile(File, 8, True, True)

    ' Just for the example:
    myrange1 = 10
    box = ""

    For i = 1 To myrange1
    box = ""
         For j = 1 To 10
           box = box & Sheet1.Cells(i, j) & ChrW(44)
         Next j
    MyFile.WriteLine box
    Next i

    MyFile.Close
End Sub

the file which I creat is not a realy csv. can anybody help me? if I double click csv file, it will appeare like this. each line appear in one cell not in ten different cells.

a,s,d,sdf,sdf,dsdd,dd,dsd,sd,sds,

a,s,d,sdf,sdf,dsdd,dd,dsd,sd,sds,

a,s,d,sdf,sdf,dsdd,dd,dsd,sd,sds,

a,s,d,sdf,sdf,dsdd,dd,dsd,sd,sds,

a,s,d,sdf,sdf,dsdd,dd,dsd,sd,sds,

a,s,d,sdf,sdf,dsdd,dd,dsd,sd,sds,

a,s,d,sdf,sdf,dsdd,dd,dsd,sd,sds,

a,s,d,sdf,sdf,dsdd,dd,dsd,sd,sds,

a,s,d,sdf,sdf,dsdd,dd,dsd,sd,sds,

a,s,d,sdf,sdf,dsdd,dd,dsd,sd,sds,

Answer 1

Your current problem is nothing to do with quotes. You need quotes around a field only when that field contains any commas/semicolons, line breaks, and quotes. If it contains any quotes, those quotes must appear TWICE e.g. field containing He said "Hello" should appear as "He said ""Hello""".

You do need to find out what is the separator character in your locale. One way to find out is to start Excel, type some data into a few cells, then save it as CSV. Exit Excel and look at the file using Notepad.

By the way, you are putting out an extra comma at the end; that will make your file look like it has 11 columns, with the 11th one empty.

Would "," be slightly more readable than ChrW(44)?