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