使用VBScript将xls转换为csv并用分号分隔

Question

我有一个VBScript代码片段，可以将我的xls和xlsx文件转换为csv文件。但是，我希望每个单元格都由分号而不是逗号分隔。在我的计算机上，列表分隔符设置为分号而不是逗号，因此当我打开Excel窗口并进行保存为CSV时，它会用分号分隔。但是，我的VBScript生成的CSV文件以逗号分隔。我在网上找到了这个代码片段，因为我不太了解VBScript（我主要是Java程序员）。如何更改代码片段以使CSV文件使用分号而不是逗号分隔？

if WScript.Arguments.Count < 2 Then
WScript.Echo "Error! Please specify the source path and the destination. Usage: XlsToCsv SourcePath.xls Destination.csv"
Wscript.Quit
End If
Dim oExcel
Set oExcel = CreateObject("Excel.Application")
Dim oBook
Set oBook = oExcel.Workbooks.Open(Wscript.Arguments.Item(0))
oBook.SaveAs WScript.Arguments.Item(1), 6
oBook.Close False
oExcel.Quit
WScript.Echo "Done"

- Luke Mat

Answer 1

你可以保留原始脚本，只需要提供一个参数来指示必须应用本地设置。这样可以使用分号分隔符保存我的CSV文件。

if WScript.Arguments.Count < 2 Then 
  WScript.Echo "Error! Please specify the source path and the destination. Usage: XlsToCsv SourcePath.xls Destination.csv" 
  Wscript.Quit 
End If 
Dim oExcel 
Set oExcel = CreateObject("Excel.Application") 
oExcel.DisplayAlerts = FALSE 'to avoid prompts
Dim oBook, local
Set oBook = oExcel.Workbooks.Open(Wscript.Arguments.Item(0))
local = true 
call oBook.SaveAs(WScript.Arguments.Item(1), 6, 0, 0, 0, 0, 0, 0, 0, 0, 0, local) 'this changed
oBook.Close False 
oExcel.Quit 
WScript.Echo "Done" 

- peter

Answer 2

函数SaveAs的定义如下： .SaveAs(文件名, 文件格式, 密码, 写保护密码, 推荐只读, 创建备份, 访问模式, 冲突解决, 添加到最近使用文件列表, 文本代码页, 文本视觉布局, 地区设置)

也就是说，如果您的区域语言选项正确设置，可以使用分号。

ExcelObj.Workbooks(1).SaveAs csvFile, 6,,,,,,,,,,True

- Martin

---

谢谢你，但我不再需要这段代码了。非常感谢你。 - Luke Mat

最佳答案在这里... 你需要留意的唯一问题是你的 Excel 版本，对于 Excel 2013，在位置 12 上使用 True。 对于 Excel 2010，需要少两个参数，因此在位置 10 上。 还可以检查： http://msdn.microsoft.com/en-us/library/office/microsoft.office.interop.excel.dialogsheet.saveas.aspx 在这个上下文中... - GWD

Answer 3

在分隔文本文件中使用逗号的做法源于区域设置。虽然在美国逗号是标准的，但其他国家（如德国）则使用分号。您可以更改区域和语言设置中的列表分隔符值，然后从Excel的“另存为”窗口中选择CSV（逗号分隔）(.csv)。生成的文件将由系统设置中的任何值进行分隔。此脚本更改默认列表分隔符设置。然后打开指定的电子表格并重新保存它。最后，恢复系统设置为之前的值。

它接受两个命令行参数。第一个是输入电子表格，第二个是导出文件的输出文件名。

strDelimiter = ";"

strSystemDelimiter = ""           ' This will be used to store the current sytem value
Const HKEY_CURRENT_USER = &H80000001

' Get the current List Separator (Regional Settings) from the registry
strKeyPath = "Control Panel\International"
strValueName = "sList"
strComputer = "."
Set objRegistry = GetObject("winmgmts:\\" & strComputer & "\root\default:StdRegProv")
objRegistry.GetStringValue HKEY_CURRENT_USER, strKeyPath, strValueName, strSystemDelimiter

' Set it temporarily to our custom delimiter
objRegistry.SetStringValue HKEY_CURRENT_USER, strKeyPath, strValueName, strDelimiter

' Open spreadsheet with Excel and save it in a text delimited format
Const xlCSV = 6

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open(WScript.Arguments.Item(0))
objWorkbook.SaveAs WScript.Arguments.Item(1), xlCSV
objWorkbook.Close vbFalse         ' Prevent duplicate Save dialog
objExcel.Quit

' Reset the system setting to its original value
objRegistry.SetStringValue HKEY_CURRENT_USER, strKeyPath, strValueName, strSystemDelimiter

经过一些测试，似乎只有通过Excel的“另存为”对话框才能实现，而不能通过命令行或自动化实现。我稍微修改了脚本，使Excel窗口可见，并使用快捷键通过Excel界面打开“另存为”对话框。这应该可以解决问题。在Vista x64和Excel 2007上，它对我有效。希望对你也有用。

strDelimiter = ";"

strSystemDelimiter = ""           ' This will be used to store the current sytem value
Const HKEY_CURRENT_USER = &H80000001

' Get the current List Separator (Regional Settings) from the registry
strKeyPath = "Control Panel\International"
strValueName = "sList"
strComputer = "."
Set objRegistry = GetObject("winmgmts:\\" & strComputer & "\root\default:StdRegProv")
objRegistry.GetStringValue HKEY_CURRENT_USER, strKeyPath, strValueName, strSystemDelimiter

' Set it temporarily to our custom delimiter
objRegistry.SetStringValue HKEY_CURRENT_USER, strKeyPath, strValueName, strDelimiter

' Open spreadsheet with Excel and save it in a text delimited format
Const xlCSV = 6

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = vbTrue
Set objWorkbook = objExcel.Workbooks.Open(WScript.Arguments.Item(0))

WScript.Sleep 500                 ' Delay to make sure the Excel workbook is open
strWorkbookName = objExcel.ActiveWorkbook.Name
strTitlebar = strWorkbookName
Set WshShell = CreateObject("WScript.Shell")
WshShell.AppActivate strTitlebar  ' Make the workbook active so it receives the keystrokes
WshShell.SendKeys "%fa"           ' Keyboard shortcuts for the Save As dialog
WScript.Sleep 500
WshShell.SendKeys "%tc{ENTER}"    ' Change the Save As type to CSV
If WScript.Arguments.Count > 1 Then
    WshShell.SendKeys "+{TAB}" & WScript.Arguments.Item(1)
    WScript.Sleep 500
End If                            ' This If block changes the save name if one was provided
WshShell.SendKeys "{ENTER}"       ' Save the file
WScript.Sleep 500
WshShell.SendKeys "{ENTER}"       ' Dismiss the CSV warning dialog
Set WshShell = Nothing

objWorkbook.Close vbFalse         ' Prevent duplicate Save dialog
objExcel.Quit

' Reset the system setting to its original value
objRegistry.SetStringValue HKEY_CURRENT_USER, strKeyPath, strValueName, strSystemDelimiter

- Nilpo

---

是的，我得到了相同的结果。它用逗号分隔。我现在在学校电脑上，也许它不允许我编辑注册表键？等我回家用自己的电脑试试看。我希望这就是原因。我会在家里告诉你我的结果。 - Luke Mat

卢克，我找出了问题并在我的回复中发布了一些新代码。这应该对你有用。 - Nilpo

这个也不行。Excel文件打开了，但是代码尝试在cmd中使用“fatc”命令，然后它说“未将此项识别为内部或外部命令、可运行的程序或批处理文件。”这是一个错误的命令的常见错误信息。而且Excel窗口也关闭了。我不确定为什么会出现这个错误。然而，这个解决方案对我可能不起作用，因为似乎每个要转换的Excel文件都会打开一个Excel窗口，这是一个问题，因为我将一次性转换大约700个Excel文件...那是很多Excel窗口。 - Luke Mat

Luke，你用的是哪个版本的Windows和Excel？看起来它没有激活Excel窗口。相反，按键被发送到当前活动的窗口，这恰好是一个命令行窗口。此外，它将按顺序打开文件，因此您不应该同时打开700个Excel窗口。 - Nilpo

@LukeMat 如果没有打开Excel，这是不可能完成的。你不能使用一个没有运行的程序。至于命名，那是自动完成的。 - Nilpo

Answer 4

您可以使用FSO对象重新打开文件，然后对逗号字符进行替换（Replace()）操作。

Const OpenAsDefault = -2
Const FailIfNotExist = 0
Const ForReading = 1
Const ForWriting = 2

Set oFSO = CreateObject("Scripting.FileSystemObject")
Set fCSVFile = _
  oFSO.OpenTextFile("C:\path\file.csv", ForReading, FailIfNotExist, OpenAsDefault)

sFileContents = fCSVFile.ReadAll
fCSVFile.Close
sFileContents = Replace(sFileContents, ",",";"))

Set fCSVFile = oFSO.OpenTextFile("C:\path\file.csv", ForWriting, True)
fCSVFile.Write(sFileContents)
fCSVFile.Close

- Nathan Rice

---

这不是一个一致的解决方案，因为它还将替换字面量中的逗号：John Doe，Euro，“1,234.00” 将解析为 John Doe；Euro；“1;234.00”，可能不是用户想要的。 - AutomatedChaos

显然，如果这是数据类型，它就没什么用处了。当我发帖时，没有其他回复，所以我只是提供了一些答复。 - Nathan Rice

当然。如果用户只有一组没有逗号分隔符的有限数据，那么它可以正常工作。但是，将数据转换为正确的CSV文件是含糊不清的。甚至微软也不能跟上rfc4180格式定义。 - AutomatedChaos

是的，文件中会有货币，这就是为什么我想用分号而不是逗号进行分隔的原因。如果我不涉及货币，我会坚持使用逗号并将其保留在那里。但还是感谢你的帮助，我很感激。 - Luke Mat

Answer 5

我把参数改为 true，对我有用。

if WScript.Arguments.Count < 2 Then
    WScript.Echo "Erro! Especifique origem e destino. Exemplo: XlsToCsv SourcePath.xls Destination.csv"
    Wscript.Quit
End If
Dim oExcel
Set oExcel = CreateObject("Excel.Application")
Dim oBook
Set oBook = oExcel.Workbooks.Open(Wscript.Arguments.Item(0))
call oBook.SaveAs(WScript.Arguments.Item(1), 6, 0, 0, 0, 0, 0, 0, 0, 0, 0, true) --CHANGED
oBook.Close False
oExcel.Quit

- Rubens Wuensch