Export csv separated by columns

Question

I have an Excel with several sheets I want to export to csv delimited by columns.

When I run the code, it exports the files to csv but comma delimited, not column delimited as I export in csv.

Any help would be appreciated.

Sub SaveShtsAsBook()
    Dim Sheet As Worksheet, SheetName$, MyFilePath$, N&
    MyFilePath$ = ActiveWorkbook.path & "\" & _
    Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 5)
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
         '      End With
        On Error Resume Next '<< a folder exists
        MkDir (MyFilePath & "_csv") '<< create a folder
        For N = 1 To Sheets.Count
            Sheets(N).Activate
            SheetName = ActiveSheet.Name
            Cells.Copy
            Workbooks.Add (xlWBATWorksheet)
            With ActiveWorkbook
                With .ActiveSheet
                    .Paste
                    .Name = SheetName
                    [A1].Select
                End With
                 'save book in this folder
                .SaveAs ThisWorkbook.path & "\_csv\" & SheetName & ".csv", FileFormat:=xlCSV
                .Close SaveChanges:=True
            End With
            .CutCopyMode = False
        Next
    End With
    Sheet1.Activate
End Sub

Thanks!

Edit: Screenshot that clarifies my problem. https://i.stack.imgur.com/SsmTH.jpg

Please clarify what in your code specifies "column separated values" or some such. ".csv" specifically means "comma separated values" See for reference: [.csv](https://en.wikipedia.org/wiki/Comma-separated_values) — Tom Brunberg, Sep 04 '22 at 09:55
This link https://stackoverflow.com/questions/13496686/how-to-save-semi-colon-delimited-csv-file-using-vba has a macro which will convert a CSV file to a tilde or ~ separated file. You can easily change the ~ in the code to a semi-colon or colon etc — Cyrus, Sep 04 '22 at 10:02
I have updated the post with an image. I hope this clarifies the problem. Thanks to both of you — Antor Cha, Sep 04 '22 at 10:37
@AntorCha Your requirement is not meaningful. A csv file contains text characters only. There is no such thing as a **"column"** in a csv file. You can specify the **text character** you wish to use between each piece of data in order to specify the columns. And then the program you use to read that file needs to interpret that separator appropriately. If you want actual columns in your export, then save it as an `.xls?` file — Ron Rosenfeld, Sep 04 '22 at 13:40

score 0 · Answer 1 · answered Sep 04 '22 at 10:49

0

Define FileFormat as xlText and the file will be TAB delimited, which you obviously are looking for.

f.ex.:

Private Sub CommandButton1_Click()
    Dim wb As Workbook
    Set wb = ActiveWorkbook
    
    wb.SaveAs "c:\tmp\tabtest.csv", xlText
    
End Sub

answered Sep 04 '22 at 10:49

Tom Brunberg

20,312
8
37
54

Thanks for the help. That's the expression, but it would be column delimited, not tab delimited. Each value in a different cell. – Antor Cha Sep 04 '22 at 10:59
Well, TAB delimited files have existed before Excel was invented. That Excel uses this format for column separation is just an application convention. But you may be correct in that Excel might call it "Column delimited" because that is the purpose in Excel. – Tom Brunberg Sep 04 '22 at 11:36
Ok, now I understand it. I see that the concepts behind are the same, but I need it to be delimited by columns (each value in a cell) because each column is a variable to be merged into another document. If saving as a column delimited is not possible, do you know how to do it with code? Thank you very much. – Antor Cha Sep 04 '22 at 11:51
I consider "TAB delimited" synonym for "Column delimited" when speaking about Excel file formats. So did you try what I suggested: changing `FileFormat:=xlCSV` in your code to `FileFormat:=xlText` ? The produced file would have TAB characters between cell data and Excel should automatically read the TAB characters as "Column separators". – Tom Brunberg Sep 04 '22 at 12:18
Yes, I have tried it and it does: https://imgur.com/a/akrDh59, instead of placing a value in each column. Maybe I have something wrong in my Excel. I can convert the text into columns with the tool, but I would prefer to get it from the export. – Antor Cha Sep 04 '22 at 14:52

Export csv separated by columns

1 Answers1