Save a csv with data in columns using VBA

Question

I made a Macro in Excel-VBA that :

Creates a new worksheet
Writes data into 5 columns
Saves the file as a CSV
Closes the file

The problem is, once the file is closed, the data is no longer separated in columns. But it's comma separated and concatenated into one single column.

Oddly enough, closing the recently created csv file manually (and not closing it in VBA) solves this issue, as the data will still be in columns when opening it again..

What I'm trying to do here, is to save/close the file in VBA, and keep the data in columns.

Here's an exemple :

Sub test()
    Workbooks.Add
    Cells(1, 1) = "a"
    Cells(1, 2) = "a"
    Cells(1, 3) = "a"
    ActiveWorkbook.SaveAs filename:= "C:\Users\user\Desktop\File.csv", FileFormat:=xlCSV
End Sub

Then closing "File.csv" manually and opening it again :

Second exemple :

Sub test()
    Workbooks.Add
    Cells(1, 1) = "a"
    Cells(1, 2) = "a"
    Cells(1, 3) = "a"
    ActiveWorkbook.SaveAs filename:= "C:\Users\user\Desktop\File.csv", FileFormat:=xlCSV
    ActiveWorkbook.Close
End Sub

The file is closed automatically here. Here is the result when it is opened manually :

Add this `ActiveWorkbook.Close SaveChanges:=True` before `End Sub` in the second example. I tested that and it worked fine. — Stupid_Intern, Feb 25 '16 at 11:28
out of my head, I think you can set the separation character. In stead of using the comma, use TAB. check: http://superuser.com/questions/476327/how-do-i-save-an-excel-spreadsheet-as-a-semi-colon-separated-values-file — Eric, Feb 25 '16 at 11:28
@Eric My computer's separator is set to ";" not "," and I still get a comma. — Naucle, Feb 25 '16 at 11:39

score 4 · Accepted Answer · answered Feb 25 '16 at 11:45

4

This works for me:

Sub test()

    Workbooks.Add
    Cells(1, 1) = "a"
    Cells(1, 2) = "a"
    Cells(1, 3) = "a"

    With ActiveWorkbook
        .SaveAs Filename:="C:\Users\user\Desktop\File.csv", FileFormat:=xlCSV, Local:=True
        .Close False
    End With

End Sub

answered Feb 25 '16 at 11:45

Jaroslav Svestka

80
8

3

Yes, this will work. Explanation: Since VBA does everything in EN_US locale per default, it uses a comma as CSV delimiter per default. There is a parameter `Local` in `SaveAs` to avoid this and use the delimiter set in locale settings. But then you must close the workbook **without** saving after the `SaveAs`. If not, the `.Close SaveChanges:=True` will use the comma again. – Axel Richter Feb 25 '16 at 11:50

Save a csv with data in columns using VBA

1 Answers1