Semicolon delimited UTF8 CSV Excel using VBA and sep=;

Question

Context : I have a csv UTF8 file that is generated from values in a excel workbook. The delimiters must be semicolon for the API to take the file and process the data. This must be done automatically because this macro will be used my a lot of people.

Problem : My current settings are saving the csv UTF8 file with comma delimeters which is not working for me because of the API.

Solution : I have found that by adding in cell A1 "sep=;" I am able to bypass the current setting in excel and that it will put semicolon instead of commas.

Problem 2 : When I save the file directly in the macro, the sep=; is not working. If the user clicks on the save icon in Excel the delimiters are changing to semicolon instead of commas.

I am trying to understand why saving the file directly in the macro is not making my delimiters switch to semicolon but saving manually does.

Thanks !

Are you in control of that macro, or is it done for you ? I believe the A1 value "sep=;" is not something Excel knows about (it would be a stupid place to put configurations), but something the macro uses. If you are in control of the macro, could you paste the line where file is saved ? I think it's the difference between creating file yourself, and using Excel API function to do it. — MyICQ
Hi MyICQ, Thanks for your answer. To answer your question, yes I am in control of the macro. If I put it manually in A1, so I insert a lign and add sep=; in the cell, save the document, delete the lign and resave the document, it works fine. I added it in my macro because the goal is to do it automatically but when it's in the macro it doesn't behave the same. It's like if Excel doesn't recognize it when done in a macro vs done manually. In the macro it doesn't switch the commas to semicolon, in fact it only tells me what the current delimeters are. — fofolle10
I have not dug into the VBA to document this, but others are strugling with the same. It depends on saving or not, and changing regional settings. Cumbersome and dumb of Microsoft. I would compile the lines manually: stackoverflow.com/questions/7079009/… — MyICQ

fofolle10 fofolle10 · Accepted Answer · 2021-03-02T16:49:52

Here's the solution a member of my team found.

Sub SaveAsCSV(ByRef cells As Range, ByVal filename As String, Optional ByVal separator As String = ";")


Dim csv As String
Dim r As range, c As range
For Each r In cells.Rows
    For Each c In r.Columns
         If Application.IsText(c) Then
            csv = csv & Chr(34) & c & Chr(34)
         Else
            csv = csv & c
        End If
        csv = csv & separator
    Next
    csv = Left(csv, Len(csv) - Len(separator))
    csv = csv & Chr(13)
Next
    
Dim file As Object
Set file = CreateObject("adodb.stream")
file.Type = 2
file.Mode = 3
file.Charset = "UTF-8"
file.Open
file.WriteText (csv)
file.SaveToFile (filename)
file.Close
End Sub

Semicolon delimited UTF8 CSV Excel using VBA and sep=;

1 Answers