blocks|key|1206292|text|谢谢你贴出这个问题和解决方案。这对我有很大帮助。是的，我还发现SaveAs没有在UTF8中保存CSV文件。在我的例子中，它使用shift-JIS。adodb.stream对我来说很好。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1206293|但是，我不知道为什么，但我必须声明您在代码中使用的一些常量(enum)。(我对VBA非常陌生，所以我可能忽略了为什么会发生这种情况)。我在函数的开头添加了这个，然后它完美地工作了：|1206294|++Const+adTypeText+=+2
++Const+adModeReadWrite+=+3
++Const+adTypeBinary+=+1
++Const+adLF+=+10
++Const+adSaveCreateOverWrite+=+2
++Const+adWriteLine+=+1|code-block|syntax|javascript|1206295|我从微软文档中得到了价值。再一次，谢谢！|1206296|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|O|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

thank you for posting this question and also the solution. It helped me a lot.
Yes, I also found that SaveAs does not save the CSV file in UTF8. In my case it uses shift-JIS. The adodb.stream worked well for me.

However, I am not sure why but I had to declare some constants (enum) you used in the code. (I am really new to VBA so maybe I missed something about why this happens). I added this in the beginning of the function, then it worked perfectly:

<pre><code> Const adTypeText = 2
 Const adModeReadWrite = 3
 Const adTypeBinary = 1
 Const adLF = 10
 Const adSaveCreateOverWrite = 2
 Const adWriteLine = 1
</code></pre>

I got the value from Microsoft docs.
Once again, thanks!

blocks|key|159909|text|所以我再次遇到了需要这段代码的情况，我读了评论和Leonard的答案，这让我更新了我的代码，并提供了更好的描述。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|159910|此代码将转换您的Excel表，并将它保存为一个CSV文件与UTF-8没有BOM编码。我在一个网站上找到了这段代码，所以我不会因此而受到赞扬。无BOM链路的CSV|offset|length|159911|Option+Explicit

Sub+CSVFileAsUTF8WithoutBOM()
Dim+SrcRange+As+Range
Dim+CurrRow+As+Range
Dim+CurrCell+As+Range
Dim+CurrTextStr+As+String
Dim+ListSep+As+String
Dim+FName+As+Variant
Dim+UTFStream+As+Object
Dim+BinaryStream+As+Object

'+ADO+Constants
Const+adTypeBinary+=+1+'+The+stream+contains+binary+data
Const+adTypeText+=+2+'+The+stream+contains+text+data+(default)
Const+adWriteLine+=+1+'+write+text+string+and+a+line+separator+(as+defined+by+the+LineSeparator+property)+to+the+stream.
Const+adModeReadWrite+=+3+'+Read/write
Const+adLF+=+10+'+Line+feed+only+-+default+is+carriage+return+line+feed+(adCRLF)
Const+adSaveCreateOverWrite+=+2+'+Overwrites+the+file+with+the+data+from+the+currently+open+Stream+object,+if+the+file+already+exists

'+Open+this+workbook+location
ChDrive+Left(ThisWorkbook.Path,+1)
ChDir+ThisWorkbook.Path

'+ask+for+file+name+and+path
++FName+=+Application.GetSaveAsFilename("",+"CSV+File+(*.csv),+*.csv")

'+prepare+UTF-8+stream
++Set+UTFStream+=+CreateObject("adodb.stream")
++UTFStream.Type+=+adTypeText
++UTFStream.Mode+=+adModeReadWrite
++UTFStream.Charset+=+"UTF-8"
++UTFStream.LineSeparator+=+adLF
++UTFStream.Open

++'set+field+separator
++ListSep+=+";"
++'set+source+range+with+data+for+csv+file
++If+Selection.Cells.Count+>+1+Then
++++Set+SrcRange+=+Selection
++Else
++++Set+SrcRange+=+ActiveSheet.UsedRange
++End+If

++For+Each+CurrRow+In+SrcRange.Rows
++++'enclose+each+value+with+quotation+marks+and+escape+quotation+marks+in+values
++++CurrTextStr+=+""
++++For+Each+CurrCell+In+CurrRow.Cells
++++++CurrTextStr+=+CurrTextStr+&+""""+&+Replace(CurrCell.Value,+"""",+"""""")+&+""""+&+ListSep
++++Next
++++'remove+ListSep+after+the+last+value+in+line
++++While+Right(CurrTextStr,+1)+=+ListSep
++++++CurrTextStr+=+Left(CurrTextStr,+Len(CurrTextStr)+-+1)
++++Wend
++++'add+line+to+UTFStream
++++UTFStream.WriteText+CurrTextStr,+adWriteLine+'+Writes+character+data+to+a+text+Stream+object
++Next

++'skip+BOM
++UTFStream.Position+=+3+'+sets+or+returns+a+long+value+that+indicates+the+current+position+(in+bytes)+from+the+beginning+of+a+Stream+object

++'copy+UTFStream+to+BinaryStream
++Set+BinaryStream+=+CreateObject("adodb.stream")
++BinaryStream.Type+=+adTypeBinary
++BinaryStream.Mode+=+adModeReadWrite
++BinaryStream.Open+'+Opens+a+Stream+object

++'Strips+BOM+(first+3+bytes)
++UTFStream.CopyTo+BinaryStream+'+Copies+a+specified+number+of+characters/bytes+from+one+Stream+object+into+another+Stream+object

++UTFStream.Flush+'+Sends+the+contents+of+the+Stream+buffer+to+the+associated+underlying+object
++UTFStream.Close+'+Closes+a+Stream+object

++'save+to+file
++BinaryStream.SaveToFile+FName,+adSaveCreateOverWrite
++BinaryStream.Flush+'+Sends+the+contents+of+the+Stream+buffer+to+the+associated+underlying+object
++BinaryStream.Close+'+Closes+a+Stream+object

End+Sub|code-block|syntax|javascript|159912|entityMap|0|LINK|mutability|MUTABLE|url|https://www.google.cz/url?sa=t&rct=j&q=&esrc=s&source=web&cd=1&cad=rja&uact=8&ved=0CCEQFjAA&url=http%253A%252F%252Fvba-corner.livejournal.com%252F5733.html&ei=OOEkVf31O8alsAHUnIHgBA&usg=AFQjCNHm2E0tNI8e3qolNR77dJGsLK3nIQ&sig2=aILAtb4800hyo7qdbuMydQ^0|0|1Y|A|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@]|9|@$D|U|E|V|1|W]]|A|$]]|$1|F|3|G|5|H|7|X|8|@]|9|@]|A|$I|J]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|R]]]]

So I came in the situation where I needed this code again, and I read the comments and Leonard's answer, which made me update my code together with better descriptions. 

This code will convert your Excel sheet and save it as a CSV file with the UTF-8 without BOM encoding. I found this code on a website, so I will not take credit for it. <a href="https://www.google.cz/url?sa=t&amp;rct=j&amp;q=&amp;esrc=s&amp;source=web&amp;cd=1&amp;cad=rja&amp;uact=8&amp;ved=0CCEQFjAA&amp;url=http%3A%2F%2Fvba-corner.livejournal.com%2F5733.html&amp;ei=OOEkVf31O8alsAHUnIHgBA&amp;usg=AFQjCNHm2E0tNI8e3qolNR77dJGsLK3nIQ&amp;sig2=aILAtb4800hyo7qdbuMydQ" rel="nofollow noreferrer">CSV without BOM link</a>

<pre><code>Option Explicit

Sub CSVFileAsUTF8WithoutBOM()
Dim SrcRange As Range
Dim CurrRow As Range
Dim CurrCell As Range
Dim CurrTextStr As String
Dim ListSep As String
Dim FName As Variant
Dim UTFStream As Object
Dim BinaryStream As Object

' ADO Constants
Const adTypeBinary = 1 ' The stream contains binary data
Const adTypeText = 2 ' The stream contains text data (default)
Const adWriteLine = 1 ' write text string and a line separator (as defined by the LineSeparator property) to the stream.
Const adModeReadWrite = 3 ' Read/write
Const adLF = 10 ' Line feed only - default is carriage return line feed (adCRLF)
Const adSaveCreateOverWrite = 2 ' Overwrites the file with the data from the currently open Stream object, if the file already exists

' Open this workbook location
ChDrive Left(ThisWorkbook.Path, 1)
ChDir ThisWorkbook.Path

' ask for file name and path
 FName = Application.GetSaveAsFilename("", "CSV File (*.csv), *.csv")

' prepare UTF-8 stream
 Set UTFStream = CreateObject("adodb.stream")
 UTFStream.Type = adTypeText
 UTFStream.Mode = adModeReadWrite
 UTFStream.Charset = "UTF-8"
 UTFStream.LineSeparator = adLF
 UTFStream.Open

 'set field separator
 ListSep = ";"
 'set source range with data for csv file
 If Selection.Cells.Count &gt; 1 Then
 Set SrcRange = Selection
 Else
 Set SrcRange = ActiveSheet.UsedRange
 End If

 For Each CurrRow In SrcRange.Rows
 'enclose each value with quotation marks and escape quotation marks in values
 CurrTextStr = ""
 For Each CurrCell In CurrRow.Cells
 CurrTextStr = CurrTextStr &amp; """" &amp; Replace(CurrCell.Value, """", """""") &amp; """" &amp; ListSep
 Next
 'remove ListSep after the last value in line
 While Right(CurrTextStr, 1) = ListSep
 CurrTextStr = Left(CurrTextStr, Len(CurrTextStr) - 1)
 Wend
 'add line to UTFStream
 UTFStream.WriteText CurrTextStr, adWriteLine ' Writes character data to a text Stream object
 Next

 'skip BOM
 UTFStream.Position = 3 ' sets or returns a long value that indicates the current position (in bytes) from the beginning of a Stream object

 'copy UTFStream to BinaryStream
 Set BinaryStream = CreateObject("adodb.stream")
 BinaryStream.Type = adTypeBinary
 BinaryStream.Mode = adModeReadWrite
 BinaryStream.Open ' Opens a Stream object

 'Strips BOM (first 3 bytes)
 UTFStream.CopyTo BinaryStream ' Copies a specified number of characters/bytes from one Stream object into another Stream object

 UTFStream.Flush ' Sends the contents of the Stream buffer to the associated underlying object
 UTFStream.Close ' Closes a Stream object

 'save to file
 BinaryStream.SaveToFile FName, adSaveCreateOverWrite
 BinaryStream.Flush ' Sends the contents of the Stream buffer to the associated underlying object
 BinaryStream.Close ' Closes a Stream object

End Sub
</code></pre>

So this was my initial question. The answer to my question below, seems to be that the only solution to get UTF-8 (and UTF-8 without BOM) encoding, is to use the ADODB.Stream object. The answer to my new question in the subject line is posted as a code. 
 I am sitting here and trying to <code>Save</code> an Excel sheet as a <code>.CSV</code>-file with a VBA macro. 
However, I am wondering if it matters whether I use <code>ADODB</code>/<code>ADODB.Stream</code> or just <code>.SaveAs</code> <code>Fileformat:=xlCSV</code>. I have tried to Google it, and it seems like I cannot find an answer to which method is the "best". I would need it to be comma delimited, UTF-8, and double quotations ("") as text-identifier. 
 
Is it correct that when you use <code>Fileformat:=</code>, it is not possible to <code>SaveAs</code> UTF-8, since the <code>xlCSV</code> is not using that encoding? YES, that is correct. 

See my answer for the solution.

How to save a text file (CSV) with UTF-8 without BOM encoding in VBA (Excel)?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋 

腾讯云代码助手

CODING DevOps

Cloud Studio

SDK中心

API中心

命令行工具

这是我的第一个问题。对我下面问题的答案似乎是，获得UTF-8 (和UTF-8没有BOM)编码的唯一解决方案是使用ADODB.Stream对象。标题中我的新问题的答案是以代码形式发布的。我坐在这里，试图将一个Save表作为一个带有VBA宏的.CSV_-文件。但是，我想知道我是使用ADODB/ADODB.Stream还是只...

问如何在VBA (Excel)中保存没有BOM编码的UTF-8文本文件(CSV)？
EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在VBA (Excel)中保存没有BOM编码的UTF-8文本文件(CSV)？EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在VBA (Excel)中保存没有BOM编码的UTF-8文本文件(CSV)？
EN