将工作表导出为UTF-8 CSV文件(使用Excel-VBA) [英] Export sheet as UTF-8 CSV file (using Excel-VBA)

问题描述

我想导出使用VBA在UTF-8 CSV中创建的文件.通过搜索留言板，我发现以下代码将文件转换为UTF-8(从此线程):

I would like to export a file I have created in UTF-8 CSV using VBA. From searching message boards, I have found the following code that converts a file to 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，它将丢失所有包含的特殊字符，从而使过程毫无意义！

However, this code only converts a non-UTF-8 file to UTF-8. If I were to save my file in non-UTF-8 and then convert it to UTF-8, it would have already lost all the special characters it contained, thus rendering the process pointless!

我要执行的操作是以UTF-8(CSV)保存打开的文件.使用VBA可以做到这一点吗?

What I'm looking to do is save an open file in UTF-8 (CSV). Is there any way of doing this with VBA?

n.b.我也在'ozgrid'论坛上问了这个问题.如果找到解决方案，将同时关闭两个线程.

n.b. I have also asked this question on the 'ozgrid' forum. Will close both threads together if I find a solution.

推荐答案

最后在Office 2016中，您可以在UTF8中简单地将savs保存为CSV.

Finally in Office 2016, you can simply savs as CSV in UTF8.

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中.

This will save the worksheet 1 into csv named test.

这篇关于将工作表导出为UTF-8 CSV文件(使用Excel-VBA)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！