将表格导出为UTF-8格式的CSV文件（使用Excel-VBA）

Question

将表格导出为UTF-8格式的CSV文件（使用Excel-VBA）

8

我想使用VBA导出一个我已经创建的UTF-8 CSV文件。从搜索论坛中，我找到了以下代码，可以将文件转换为UTF-8(从这个帖子)：

Sub SaveAsUTF8() 

    Dim fsT, tFileToOpen, tFileToSave As String 

    tFileToOpen = InputBox("Enter the name and location of the file to convert" & vbCrLf & "With full path and filename ie. C:\MyFolder\ConvertMe.Txt") 
    tFileToSave = InputBox("Enter the name and location of the file to save" & vbCrLf & "With full path and filename ie. C:\MyFolder\SavedAsUTF8.Txt") 

    tFileToOpenPath = tFileToOpen 
    tFileToSavePath = tFileToSave 

Set fsT = CreateObject("ADODB.Stream"): 'Create Stream object
fsT.Type = 2: 'Specify stream type – we want To save text/string data.
fsT.Charset = "utf-8": 'Specify charset For the source text data.

fsT.Open: 'Open the stream
fsT.LoadFromFile tFileToOpenPath: 'And write the file to the object stream

fsT.SaveToFile tFileToSavePath, 2: 'Save the data to the named path

End Sub

然而，这段代码只能将非UTF-8文件转换为UTF-8。如果我将我的文件保存为非UTF-8，然后再将其转换为UTF-8，那么它已经丢失了其中包含的所有特殊字符，从而使该过程毫无意义！

我想要做的是将一个打开的文件保存为UTF-8（CSV）。有没有办法在VBA中实现这一点？

注：我也在'ozgrid'论坛上提出了这个问题。如果找到解决方案，将同时关闭两个线程。

- CaptainProg

1

我的示例将在Excel中导出一个范围到UTF-8 CSV https://dev59.com/sWjWa4cB1Zd3GeqPo0Xd#12353832。有一些更新，可以转换http、字符串或允许您指定范围。 - user857521

或者试试这个：http://www.mediafire.com/view/?zbngcy2sborbklm - user857521

由于我遇到了完全相同的问题，我看到了你的留言，然后在一篇法国网站上找到了答案！http://geek-mondain.blogspot.fr/2011/09/excel-et-son-incapacite-exporter-des.html 它完美地解决了我的问题！ - Rémi

3个回答

4

更新此代码。我使用这个代码来更改指定文件夹（标记为“Bron”）中的所有.csv文件，并将它们保存为另一个文件夹（标记为“doel”）中的csv utf-8格式。

Sub SaveAsUTF8()

Dim fsT As Variant, tFileToOpen As String, tFileToSave As String
Dim Message As String
Dim wb As Workbook
Dim fileName As String

Set wb = ActiveWorkbook

With Application
.ScreenUpdating = False
.DisplayAlerts = False
End With

Message = "Source folder incorrect"
SourceFolder = wb.Worksheets("Menu").Range("Bron") & "\"
If Dir(SourceFolder, vbDirectory) = "" Or IsEmpty(SourceFolder) Then GoTo errorhandler

Message = "Target folder incorrect"
TargetFolder = wb.Worksheets("Menu").Range("Doel") & "\"
If Dir(TargetFolder, vbDirectory) = "" Or IsEmpty(TargetFolder) Then GoTo errorhandler

fileName = Dir(SourceFolder & "\*.csv", vbNormal)

Message = "No files available."
If Len(fileName) = 0 Then GoTo errorhandler

Do Until fileName = ""

    tFileToOpen = SourceFolder & fileName
    tFileToSave = TargetFolder & fileName

    tFileToOpenPath = tFileToOpen
    tFileToSavePath = tFileToSave

Set fsT = CreateObject("ADODB.Stream"): 'Create Stream object
fsT.Type = 2: 'Specify stream type – we want To save text/string data.
fsT.Charset = "utf-8": 'Specify charset For the source text data.

fsT.Open: 'Open the stream
fsT.LoadFromFile tFileToOpenPath: 'And write the file to the object stream

fsT.SaveToFile tFileToSavePath, 2: 'Save the data to the named path

fileName = Dir()

Loop

Message = "Okay to remove all old files?"
If QuestionMessage(Message) = False Then
    GoTo the_end
Else
    On Error Resume Next
    Kill SourceFolder & "*.csv"
    On Error GoTo errorhandler
End If

the_end:
With Application
.ScreenUpdating = True
.DisplayAlerts = True
End With
Exit Sub

errorhandler:
With Application
.ScreenUpdating = True
.DisplayAlerts = True
End With
CriticalMessage (Message)
Exit Sub

End Sub

'----------

Function CriticalMessage(Message As String)

MsgBox Message

End Function

'----------

Function QuestionMessage(Message As String)

If MsgBox(Message, vbQuestion + vbYesNo) = vbNo Then
QuestionMessage = False
Else
QuestionMessage = True
End If

End Function

- Alex

2

这是基于Excel VBA - export to UTF-8的解决方案，由之前链接到的user3357963提供。它包括导出范围和选择的宏。

Option Explicit

Const strDelimiter = """"
Const strDelimiterEscaped = strDelimiter & strDelimiter
Const strSeparator = ","
Const strRowEnd = vbCrLf
Const strCharset = "utf-8"

Function CsvFormatString(strRaw As String) As String

    Dim boolNeedsDelimiting As Boolean

    boolNeedsDelimiting = InStr(1, strRaw, strDelimiter) > 0 _
        Or InStr(1, strRaw, Chr(10)) > 0 _
        Or InStr(1, strRaw, strSeparator) > 0

    CsvFormatString = strRaw

    If boolNeedsDelimiting Then
        CsvFormatString = strDelimiter & _
            Replace(strRaw, strDelimiter, strDelimiterEscaped) & _
            strDelimiter
    End If

End Function

Function CsvFormatRow(rngRow As Range) As String

    Dim arrCsvRow() As String
    ReDim arrCsvRow(rngRow.Cells.Count - 1)
    Dim rngCell As Range
    Dim lngIndex As Long

    lngIndex = 0

    For Each rngCell In rngRow.Cells
        arrCsvRow(lngIndex) = CsvFormatString(rngCell.Text)
        lngIndex = lngIndex + 1
    Next rngCell


    CsvFormatRow = Join(arrCsvRow, ",") & strRowEnd

End Function

Sub CsvExportRange( _
        rngRange As Range, _
        Optional strFileName As Variant _
    )

    Dim rngRow As Range
    Dim objStream As Object

    If IsMissing(strFileName) Or IsEmpty(strFileName) Then
        strFileName = Application.GetSaveAsFilename( _
            InitialFileName:=ActiveWorkbook.Path & "\" & rngRange.Worksheet.Name & ".csv", _
            FileFilter:="CSV (*.csv), *.csv", _
            Title:="Export CSV")
    End If

    Set objStream = CreateObject("ADODB.Stream")
    objStream.Type = 2
    objStream.Charset = strCharset
    objStream.Open

    For Each rngRow In rngRange.Rows
        objStream.WriteText CsvFormatRow(rngRow)
    Next rngRow

    objStream.SaveToFile strFileName, 2
    objStream.Close

End Sub

Sub CsvExportSelection()
    CsvExportRange ActiveWindow.Selection
End Sub

Sub CsvExportSheet(varSheetIndex As Variant)

    Dim wksSheet As Worksheet
    Set wksSheet = Sheets(varSheetIndex)

    CsvExportRange wksSheet.UsedRange

End Sub

- Bryn

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Raymond · Accepted Answer

最后在 Office 2016 中，您可以简单地将文件另存为 UTF8 格式的 CSV 文件。

Sub SaveWorkSheetAsCSV()

Dim wbNew As Excel.Workbook
Dim wsSource As Excel.Worksheet, wsTemp As Excel.Worksheet
Dim name As String



    Set wsSource = ThisWorkbook.Worksheets(1)
    name = "test"
    Application.DisplayAlerts = False 'will overwrite existing files without asking
    Set wsTemp = ThisWorkbook.Worksheets(1)
    Set wbNew = ActiveWorkbook
    Set wsTemp = wbNew.Worksheets(1)
    wbNew.SaveAs name & ".csv", xlCSVUTF8 'new way
    wbNew.Close
    Application.DisplayAlerts = True

End Sub

这将把工作表1保存为名为test的csv文件。