excelvbacsv

Can I prevent my csv file from creating quotation marks?


I have a coordinate list (x,y,z) that I need to save as a csv file. It is sorted in Excel, but when I save as csv, it is separated by a semicolon due to the regional settings in Windows. These settings should not be changed. Then I thought that I could concatenate the three coordinates, with a comma between them. When I subsequently save as csv manually, it works perfectly :-) However, I would like to have a macro to save as csv, but then quotes are put around the text. I understand that this is because the cells are read as text, and I cannot prevent this :-( The csv file needs to be imported into another program that can only load the list, if it is xx.xx,yy.yy,zz.zz. It is easy enough to open the csv file in notepad and use replace, but that was not the intention. Is there a way I can get around the problem with semicolons or quotes?


Solution

  • For simple csv consider writing line by line

    Option Explicit
    
    Sub CreateCSV()
    
        Const CSVFILE = "mycsvfile.csv"
    
        Dim r As Long, lastrow As Long
        Dim fso, ts, ar
    
        ' copy data to array
        With ThisWorkbook.Sheets("Sheet1")
           lastrow = .Cells(.Rows.Count, 1).End(xlUp).Row
           ar = .Range("A1:C" & lastrow)
        End With
        
        ' create csv
        Set fso = CreateObject("Scripting.FilesystemObject")
        Set ts = fso.createTextFile(CSVFILE, True, True) 'overwrite,Unicode
           
        For r = 1 To UBound(ar)
            ts.writeline ar(r, 1) & "," & ar(r, 2) & "," & ar(r, 3)
        Next
       
        ' end
        ts.Close
        MsgBox UBound(ar) & " rows exported to " & CSVFILE, vbInformation
    
    End Sub