如何使用 VBA 保存分号分隔的 csv 文件？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13496686/
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
How to save semi-colon delimited csv file using VBA?
提问by user1758952
I copy data into a spreadsheet, use VBA to format it, then save that sheet into a CSV file.
我将数据复制到电子表格中，使用 VBA 对其进行格式化，然后将该表格保存到 CSV 文件中。
I use the following code:
我使用以下代码：
ws.SaveAs Filename:=filestr, Fileformat:=xlCSV
ws is the worksheet that I saved.
ws 是我保存的工作表。
This gives me a comma-delimited CSV file.
这给了我一个逗号分隔的 CSV 文件。
I would like to save that sheet into a semicolon-delimited file.
我想将该表保存到以分号分隔的文件中。
I found the following:
我发现了以下内容：
- Go to Start>Settings>Regional And Language Options
- Click on the Customize button
- Next to List Separator type in a semi-colon (;)
- 转到开始>设置>区域和语言选项
- 单击自定义按钮
- 在分号 (;) 中的列表分隔符类型旁边
I followed the procedure above and changed my code to:
我按照上面的步骤将代码更改为：
ws.SaveAs Filename:=filestr, Fileformat:=xlCSV, Local:=True
I still get a comma-delimited CSV file as output.
我仍然得到一个逗号分隔的 CSV 文件作为输出。
I am using Excel 2003 and my OS is Windows XP.
我使用的是 Excel 2003，我的操作系统是 Windows XP。
回答by user2726096
i've just checked this because had same problem. Filename has no functionality in this case.
我刚刚检查了这个，因为有同样的问题。在这种情况下，文件名没有任何功能。
This is what worked for me:
这对我有用：
With ActiveWorkbook
.SaveAs Filename:="My File.csv", FileFormat:=xlCSV, Local:=True
.Close False
End With
In regional settings -> ; <- as list separator. It is also important not to save changes when closing -> with Close you have to use False.
在区域设置中 -> ; <- 作为列表分隔符。关闭时不要保存更改也很重要 -> 使用 Close 你必须使用False.
回答by Leo
No need to declare all this variables, just add local:=true in the end of your SaveAs method, like so:
无需声明所有这些变量，只需在 SaveAs 方法的末尾添加 local:=true ，如下所示：
ActiveWorkbook.SaveAs Filename:="C:/Path/TryMe.csv", FileFormat:=xlCSV, Local:=True
回答by JGlass
I ran into the same issue and after contemplating trying to change the "line separator" in Regional Settings using VBA code and Kernel calls I decided it would be way more of a pain, so instead I just found some examples of using the Scripting.FileSystemObject to accomplish my needs instead.
The following code will take an existing csv file and replace all the commas with the tilde "~" character.
我遇到了同样的问题，在考虑尝试使用 VBA 代码和内核调用更改区域设置中的“行分隔符”后，我认为这会更痛苦，所以我只是找到了一些使用 Scripting.FileSystemObject 的示例来完成我的需求。
以下代码将采用现有的 csv 文件并用波浪号“~”字符替换所有逗号。
Private Sub commaReplace()
Dim objFSO
Dim filePath
Dim migratorFileName
Dim strFullPath1
Dim strFullPath2
Const ForReading = 1
'define a TextStream object
Dim objTS
Dim strContents As String
'note, my code actually uses the below commented out filepath
'as the location of the workbook can be arbitrary, e.g.
'Worksheets("FilePath").[A2:A2].Value is determined when workbook
'is opened
'filePath = Worksheets("FilePath").[A2:A2].Value
filePath = "C:\Temp\"
'our original file that we've exported as csv file in another section of code
migratorFileName = "MigratorInput.csv"
strFullPath1 = filePath + migratorFileName
'the path and file name we want to save to, tilde separated vs. comma
migratorFileName = "MigratorInput.tilde.csv"
strFullPath2 = filePath + migratorFileName
'read everything from the csv file, replacing comma with tilde
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTS = objFSO.OpenTextFile(strFullPath1, ForReading)
strContents = objTS.ReadAll
strContents = Replace(strContents, ",", "~")
objTS.Close
'write everything out to another file, note, this could just overwrite
'the original file if you pass the optional overwrite flag
Set objTS = objFSO.CreateTextFile(strFullPath2)
objTS.Write strContents
objTS.Close
End Sub
You can then just call the commaReplace sub routine from your sub routine which is creating the csv file.
然后，您可以从创建 csv 文件的子例程中调用逗号替换子例程。
Hope it helps someone!
希望它可以帮助某人！
回答by Vincent
Use xlSCVMSDOS instread of xlCSV
使用 xlSCVMSDOS 代替 xlCSV
ActiveWorkbook.SaveAs Filename:="my File.csv", FileFormat:= xlCSVMSDOS, Local:=True
It worked for me
它对我有用
回答by Andrey
To use vbs script following construction succeded:
要在构建成功后使用 vbs 脚本：
.SaveAs Filename, 6, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1
.SaveAs 文件名, 6, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1
where arguments are:
其中参数是：
Object Filename,
Object FileFormat,
Object Password,
Object WriteResPassword,
Object ReadOnlyRecommended,
Object CreateBackup,
XlSaveAsAccessMode AccessMode,
Object ConflictResolution,
Object AddToMru,
Object TextCodepage,
Object TextVisualLayout,
Object Local
SourceLink : https://msdn.microsoft.com/ru-ru/library/microsoft.office.tools.excel.workbook.saveas.aspx
源链接：https://msdn.microsoft.com/ru-ru/library/microsoft.office.tools.excel.workbook.saveas.aspx
Last "1" in "SaveAs" function is equal to Local=True
“另存为”函数中的最后一个“1”等于 Local=True
Also, the semicolon must be defined as the list separator in OS regional settings (see answers above)
此外，分号必须定义为操作系统区域设置中的列表分隔符（请参阅上面的答案）
回答by bik128
Just use this code: ActiveWorkbook.SaveAs "My File.csv", xlCSV, Local:=True
只需使用此代码：ActiveWorkbook.SaveAs "My File.csv", xlCSV, Local:=True
(don't use: Filename:= )
（不要使用：文件名：=）
