vba 使用分号分隔符另存为 CSV
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19265636/
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
Save as CSV with semicolon separator
提问by saknar namn
I'm currently use this function for saving, but I have a problem with it:
我目前正在使用此功能进行保存，但我遇到了问题：
Private Sub spara()
ActiveWorkbook.SaveAs Filename:="T:\filepath+ ActiveWorkbook.Name", _
FileFormat:=xlCSV, CreateBackup:=False
End Sub
It automatically saves with ,but I need it to save with ;in its file. Is it possible to change how it saves somehow?
它会自动保存，,但我需要将其保存;在其文件中。是否有可能以某种方式改变它的保存方式？
I've tried googling around for this issue, but all macros regarding .csv file saving are just how to save in .csv and how to split multiple sheets to .csv .
我试过在谷歌上搜索这个问题，但所有关于 .csv 文件保存的宏只是如何保存在 .csv 以及如何将多个工作表拆分为 .csv 。
回答by Christian Sauer
Which language does your Excel use? If your native language uses ";" as default, you can pass the parameter "local:=True"
您的 Excel 使用哪种语言？如果您的母语使用“;” 默认情况下，您可以传递参数“local:=True”
ActiveWorkbook.SaveAs Filename:="C:\Temp\Fredi.csv", FileFormat:=xlCSV, CreateBackup:=False, local:=True
If not, your only choice is searching and replacing afterwards...
如果没有，您唯一的选择就是在之后搜索和替换...
回答by pnuts
Change Excel Options (Advanced, Editing options) to set Decimal separator to ,(obviously (!))
更改 Excel 选项（高级、编辑选项）以将小数分隔符设置为,（显然是 (!)）
回答by Jason
I had been looking this up to help resolve a similar issue I was having, I had an excel sheet that I export to a csv file, this is then uloaded elsewhere but requires the use of semicolons rather than commas for the character seperation, this worked fine when i was manually exporting the file as i had already changed the following Control Panel >> Region and Language >> additonal settings >> List separator from comma to semicolon. But when i tried to automate via VBA it defaulted back to comma, to fix I added the local paprameter as suggested by Christian Sauer above which then picks up the fact that i have changed my regional settings.
我一直在查找它以帮助解决我遇到的类似问题，我有一个导出到 csv 文件的 excel 表，然后将其加载到其他地方，但需要使用分号而不是逗号来分隔字符，这有效当我手动导出文件时很好，因为我已经更改了以下控制面板 >> 区域和语言 >> 附加设置 >> 列表分隔符从逗号到分号。但是，当我尝试通过 VBA 进行自动化时，它默认为逗号，为了修复，我添加了上面 Christian Sauer 建议的本地参数，然后发现我更改了区域设置的事实。
ActiveWorkbook.SaveAs Filename:="Filename.txt", FileFormat:=xlCSV, CreateBackup:=False, Local:=True
Thanks to Christian for the pointer.
感谢 Christian 的指点。
回答by Lionel T.
I solved it adding "SaveChanges:=False" when closing workbook
我解决了在关闭工作簿时添加“SaveChanges:=False”的问题
With ActiveWorkbook
.SaveAs Filename:="T:\filepath+ ActiveWorkbook.Name", FileFormat:=xlCSV, Local:=True
.Close SaveChanges:=False
End With
回答by Petr Abdulin
It's quite possible this problem is not solvable using Excel VBA only. The problem is, while Excel Save As...uses machine locale define list separator value, Excel VBA always uses en-US locale, thus, it always uses ,as a list separator.
很可能仅使用 Excel VBA 无法解决此问题。问题是，虽然 ExcelSave As...使用机器语言环境定义列表分隔符值，但 Excel VBA 始终使用 en-US 语言环境，因此，它始终,用作列表分隔符。
I would recommend saving a CSV and then use custom console app/script for postprocessing. There is plenty of CSV parsers available which can read a ,-csvand then save it as ;-csv.
我建议保存一个 CSV，然后使用自定义控制台应用程序/脚本进行后处理。有很多 CSV 解析器可用，它们可以读取 a,-csv然后将其另存为;-csv.
回答by dmb
For people having issues with this code
对于遇到此代码问题的人
dim wa as Workbook
Workbooks.OpenText FileName:=path2file, _
DataType:=xlDelimited, Semicolon:=True, Local:=True
set wa = ActiveWorkbook
wa.SaveAs FileName:=path2file, FileFormat:=xlCSV, _ ConflictResolution:=xlLocalSessionChanges, Local:=True
wa.Close False
The second line is really important, if wa.Close Falseis not there it will ask for approval to save, or if wa.Close Trueit will replace the ";" for ",".
第二行真的很重要，如果wa.Close False没有它会请求批准保存，或者wa.Close True它是否会替换“;” 为了 ”，”。
After going into local settings and making ";" the list delimiter, VBA was still separating with a ",". Modified the code to the above and it was done.
进入本地设置并制作“;”后 列表分隔符，VBA 仍然用“，”分隔。把上面的代码修改一下就搞定了。
Hope this throw some light for some
希望这对某些人有所启发
