utf-8 - 将工作表导出为 UTF-8 CSV 文件(使用 Excel-VBA)

Question

我想导出使用 VBA 以 UTF-8 CSV 创建的文件。通过搜索留言板，我发现了以下将文件转换为 UTF-8 的代码( from this thread ):

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 做到这一点？

n.b.我也在 'ozgrid' forum 上问过这个问题。如果我找到解决方案，将关闭两个线程。

Answer 1

最后，在 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 中。