How to export CSV file encoded with "Unicode"

Question

Currently i using VBA code to export range data to a CSV file:

Sub Fct_Export_CSV_Migration()   Dim Value As String   Dim size As Integer

  Value = ThisWorkbook.Path & "\Export_Migration" & Sheets(1).range("B20").Value & ".csv"   chemincsv = Value

  Worksheets("Correspondance Nv Arborescence").Select   Dim Plage As Object, oL As Object, oC As Object, Tmp As String, Sep$   Sep = ";"   size = Worksheets("Correspondance Nv Arborescence").range("B" & Rows.Count).End(xlUp).Row   Set Plage = ActiveSheet.range("A1:B" & size)

  Open chemincsv For Output As #1   For Each oL In Plage.Rows
    Tmp = ""
    For Each oC In oL.Cells
      Tmp = Tmp & CStr(oC.Text) & Sep
    Next


'take one less than length of the string number of characters from left, that would eliminate the trailing semicolon
    Tmp = Left(Tmp, Len(Tmp) - 1)

    Print #1, Tmp   Next   Close



  MsgBox "OK! Export to " & Value End Sub

Now, i would like to export CSV encoded with "Unicode". I think i need to use VBA function like SaveAs( xlUnicodeText ) but how to use that ?

Thx

http://stackoverflow.com/questions/4221176/excel-to-csv-with-utf8-encoding — Pankaj Jaju, Jan 16 '17 at 16:58
Possible duplicate of [Excel to CSV with UTF8 encoding](http://stackoverflow.com/questions/4221176/excel-to-csv-with-utf8-encoding) — Comintern, Jan 16 '17 at 17:14

score 1 · Accepted Answer · answered Jan 16 '17 at 17:50

Unicode CSVs are not one of the file formats supported by Excel, out of the box. This means we cannot use the SaveAs method. The good news we can work around this restriction, using VBA.

My approach uses the file system object. This incredibly handy object is great for interacting with the file system. Before you can use it you will need to add a reference:

From the VBA IDE click Tools.
Click References...
Select Windows Script Host Object Model from the list.
Press OK.

The code:

' Saves the active sheet as a Unicode CSV.
Sub SaveAsUnicodeCSV()
    Dim fso As FileSystemObject     ' Provides access to the file system.
    Dim ts As TextStream            ' Writes to your text file.
    Dim r As Range                  ' Used to loop over all used rows.
    Dim c As Range                  ' Used to loop over all used columns.

    ' Use the file system object to write to the file system.
    ' WARNING: This code will overwrite any existing file with the same name.
    Set fso = New FileSystemObject
    Set ts = fso.CreateTextFile("!!YOUR FILE PATH HERE.CSV!!", True, True)

    ' Read each used row.
    For Each r In ActiveSheet.UsedRange.Rows
        ' Read each used column.
        For Each c In r.Cells

            ' Write content to file.
            ts.Write c.Value
            If c.Column < r.Columns.Count Then ts.Write ","
        Next

        ' Add a line break, between rows.
        If r.Row < ActiveSheet.UsedRange.Count Then ts.Write vbCrLf
    Next

    ' Close the file.
    ts.Close

    ' Release object variables before they leave scope, to reclaim memory and avoid leaks.
    Set ts = Nothing
    Set fso = Nothing
End Sub

This code loops over each used row in the active worksheet. Within each row, it loops over every column in use. The contents of each cell is appended to your text file. At the end of each row, a line break is added.

To use; simply replace !!YOUR FILE PATH HERE.CSV!! with your file name.

How to export CSV file encoded with "Unicode"

1 Answers1