Search code examples
csvvbscriptxlsxlsx

Converting xls to csv using VBScript and separate by semicolons

I have a VBScript code snippet which converts my xls and xlsx files into csv files. However, I want each cell to be separated by a semicolon rather than a comma. On my computer, the list separator is set to semicolon instead of comma so when I open up an excel window and do save as csv, it separates by semicolon. However, my VBScript produces a csv file separated by commas. I found the code snippet online as I do not really know VBScript (I'm mainly a Java Programmer) that well. How can I change the code snippet to separate the csv files by semicolon rather than by comma?

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"
Solution

  • you can keep your original script, only need to give a parameter to indicate local setting must apply. This saves my CSV with a ; separator

    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"