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,

In what sense is it "not really csv" i.e. what's the problem with your output? — Murph
– Murph, Commented Jul 27, 2010 at 7:17
This might be what creates the confusion: Excel uses semicolons ; instead of commas as separators for handling CSV files. — marg
– marg, Commented Jul 27, 2010 at 7:44
@marg: The "C" in "CSV" is short for "Comma". Actually Excel uses commas except in locales where the comma is the thousands separator -- then it uses semicolon as the field separator. — John Machin
– John Machin, Commented Jul 28, 2010 at 6:25
@John: I assumed that the use of semicolons instead of commas was the standard Excel behavior for CSV files. Good to know that there are local differences. — marg
– marg, Commented Jul 28, 2010 at 7:14

John Machin · Accepted Answer · 2010-07-28 06:30:47Z

2

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)?

answered Jul 28, 2010 at 6:30

John Machin

83.3k12 gold badges147 silver badges193 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related