![导出 CSV 文件,值用双引号括起来](https://linux22.com/image/1292151/%E5%AF%BC%E5%87%BA%20CSV%20%E6%96%87%E4%BB%B6%EF%BC%8C%E5%80%BC%E7%94%A8%E5%8F%8C%E5%BC%95%E5%8F%B7%E6%8B%AC%E8%B5%B7%E6%9D%A5.png)
如何从 Excel 2004(Mac)导出值和字段都用双引号(“)括起来的 CSV 文件?
例如
"Name","Telephone"
"John Doe","555-5009"
答案1
编辑:
我只花了将近两年的时间就回来编辑了这篇文章。这里有一个解决方法,希望可以将您的所有数据括在引号中并保存为 CSV。您需要将引用添加Microsoft Scripting Runtime
到您的 VBA 项目中(在Tools
>下References...
)。
Sub addquotes()
Dim s As Worksheet
Dim tmpR As Range
Dim tmpArray() As Variant, out As String
Dim fso As FileSystemObject, ts As TextStream
Application.DisplayAlerts = False
Set s = ActiveSheet
Set tmpR = s.UsedRange
'Set format of all cells as Text.
tmpR.NumberFormat = "@"
tmpArray = tmpR.Value
For i = LBound(tmpArray, 1) To UBound(tmpArray, 1)
For j = LBound(tmpArray, 2) To UBound(tmpArray, 2)
If j = LBound(tmpArray, 2) Then out = out & Chr(34)
out = out & tmpArray(i, j) & Chr(34)
If j <> UBound(tmpArray, 2) Then
out = out & "," & Chr(34)
Else
out = out & vbLf
End If
Next j
Next i
Set fso = New FileSystemObject
Set ts = fso.OpenTextFile("C:\Users\Editor 3\Desktop\yourcsv.csv", ForWriting, True)
ts.Write out
ts.Close
Application.DisplayAlerts = True
End Sub
上次失败的尝试(仅供参考;请不要使用此):
这是一个宏,它将打开一个新工作簿并输入工作表中的所有数据,每个值都用引号括起来。然后,您可以将这个新工作簿保存为 .CSV,而不会干扰原始工作簿。即使您的原始数据包含公式,这也将有效。
Sub addquotes()
Dim s As Worksheet, newS As Worksheet
Dim tmpR As Range
Dim tmpArray() As Variant
Set s = ActiveSheet
Set tmpR = s.UsedRange
'Loads all data from sheet into array.
tmpArray = tmpR.Value
'Adds quotes around members of array if not blank.
For i = 1 To UBound(tmpArray, 1)
For j = 1 To UBound(tmpArray, 2)
If tmpArray(i, j) <> "" Then
tmpArray(i, j) = """" & tmpArray(i, j) & """"
End If
Next j
Next i
'Open new workbook and enter transformed data.
Set NewBook = Workbooks.Add
Set newS = NewBook.Sheets(1)
newS.Range("A1").Resize(UBound(tmpArray, 1), UBound(tmpArray, 2)) = tmpArray
End Sub