vba Excel：如何仅在 CSV 文件中为字符串添加双引号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/846839/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel: How to add double quotes to strings only in CSV file
提问by barrowc
I want to create a CSV file from Excel in which string values should be in double quotes and date values should be in MM/dd/yyyy format. All the numeric and Boolean values should be without quotes.
我想从 Excel 创建一个 CSV 文件，其中字符串值应使用双引号，日期值应使用 MM/dd/yyyy 格式。所有数字和布尔值都应该没有引号。
How would I go about this?
我该怎么办？
回答by Dayton Brown
It's kind of scary that Excel doesn't let you specify formats. Here's a MrExcel link that might prove useful to you as well.
Excel 不允许您指定格式，这有点可怕。这是一个 MrExcel 链接，可能对您也有用。
http://www.mrexcel.com/forum/showthread.php?t=320531
http://www.mrexcel.com/forum/showthread.php?t=320531
Here's the code from that site:
这是该网站的代码：
Sub CSVFile()
Dim SrcRg As Range
Dim CurrRow As Range
Dim CurrCell As Range
Dim CurrTextStr As String
Dim ListSep As String
Dim FName As Variant
FName = Application.GetSaveAsFilename("", "CSV File (*.csv), *.csv")
If FName <> False Then
ListSep = Application.International(xlListSeparator)
If Selection.Cells.Count > 1 Then
Set SrcRg = Selection
Else
Set SrcRg = ActiveSheet.UsedRange
End If
Open FName For Output As #1
For Each CurrRow In SrcRg.Rows
CurrTextStr = ""
For Each CurrCell In CurrRow.Cells
CurrTextStr = CurrTextStr & """" & CurrCell.Value & """" & ListSep
Next
While Right(CurrTextStr, 1) = ListSep
CurrTextStr = Left(CurrTextStr, Len(CurrTextStr) - 1)
Wend
Print #1, CurrTextStr
Next
Close #1
End If
End Sub
回答by barrowc
It's easier to use VBA to do this. The SaveAsmethod of the Workbookobject only lets you choose pre-defined formats and the xlCSVone does not delimit strings with double-quotes.
使用 VBA 更容易做到这一点。对象的SaveAs方法Workbook只允许您选择预定义的格式，xlCSV并且不使用双引号分隔字符串。
To do this in VBA:
要在 VBA 中执行此操作：
Dim fileOut As Integer
fileOut = FreeFile
Open "C:\foo.csv" For Output As #fileOut
Write #fileOut, 14, "Stack Overflow", Date, True
Close #fileOut
(NB Dateis a VBA statement that returns the current system date as a Variant of sub-type Date)
（NBDate是一个 VBA 语句，将当前系统日期作为子类型 Date 的 Variant 返回）
If you then examine the file in Notepad:
如果您随后在记事本中检查该文件：
14,"Stack Overflow",#2009-05-12#,#TRUE#
14、《堆栈溢出》,#2009-05-12#,#TRUE#
The string has been delimited as required, the date converted to universal format and both the date and boolean are delimited with # signs.
字符串已按要求分隔，日期转换为通用格式，日期和布尔值都用 # 符号分隔。
To read the data back in use the Input #statement which will interpret all of the values appropriately.
要读回数据，请使用Input #将适当解释所有值的语句。
If you want to write part of a line and then finish writing it later then:
如果你想写一行的一部分，然后再写完，那么：
Write #fileOut, 14, "Stack Overflow";
Write #fileOut, Date, True
produces the same result as the original program. The semi-colon at the end of the first statement prevents a new line being started
产生与原始程序相同的结果。第一条语句末尾的分号防止开始新的一行
Strings with embedded double-quotes will cause problems so you'll need to remove or replace those characters
带有嵌入双引号的字符串会导致问题，因此您需要删除或替换这些字符
