如何导出用“Unicode”编码的CSV文件

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

目前我使用VBA代码将范围数据导出到CSV文件:

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 ?

现在，我想导出用“Unicode”编码的CSV。我想我需要使用VBA函数SaveAs(xlUnicodeText)，但是如何使用呢?

Thx

谢谢

1 个解决方案

#1

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.

Unicode CSVs不是Excel所支持的文件格式之一，它是在box之外的。这意味着我们不能使用SaveAs方法。好消息是我们可以使用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.
从VBA IDE单击工具。
Click References...
点击引用…
Select Windows Script Host Object Model from the list.
从列表中选择Windows脚本主机对象模型。
Press OK.
按下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.

使用;简单地取代! !你的文件路径HERE.CSV ! !与你的文件名。

#1